I am starting a Power BI project that contains about 10 Excel worksheets of monthly sales and marketing data that need to be converted to tables. Currently, all my data is in rows, with the headers in Column A and each new month of data in a new column. Will Power BI work with my data in this format, or should I transpose all the data so the headers are all in a single row instead of a column?
Secondly, even if PBI will work with my data in the current format, would it be better to transpose all of it before beginning my project so it can be in the more traditional format?
Hi Will,
Yes, you can use your data. You need to bring it into Power BI using the Power Query tools and transpose and format it in a tabular format first. If you use the Get Data > More > File > From Folder technique it will automatically pick up the new data upon refresh.
However, I recommend you complete the Power Query course first. In session 2.05 I cover getting data from a folder, in session 4.18 I look at transposing and session 4.05 splitting columns, which you may also require. However, I recommend you complete the whole course because there's likely to be more Power Query tools and techniques you need to use.
So, start with the Power Query course, then move on to Power Pivot, and finally do the Power BI course.
Mynda
Thanks Mynda. I have completed the Power Query course, and am proceeding through Power Pivot. Once I finish editing my worksheets, they will be transferred to our SharePoint cloud service. To ensure I understand correctly, I can use Power Query to transpose my data instead of changing it now on the source worksheets? This brings up a couple of additional questions.
Would it be more efficient to go ahead and change my data now so I don't have to transpose it later, or does it not really matter?
Some of my worksheets contain some complex formulas that are dependent on other worksheets. Will Power Query/Power BI be able to deal with those formulas properly when transposing? For example, if I do a transpose as my formulas are structured now, I get a lot of errors because I am using relative references instead of absolute references.
Finally, since I do currently have formulas in worksheets that call data from other worksheets, should I try to eliminate this structure? Will it make it more difficult to deal with in a SharePoint environment? I don't mind doing more work up front to save me time and hassle later. I attached a sample workbook with a couple of sheets that show how I have the data currently laid out.
Hi Will,
Great to hear you're making progress through the courses.
Your file didn't get attached, however, Power Query doesn't bring in formulas, just the values that result from those formulas. So when you transpose the data it's as though the formulas don't exist, even though they remain in the source workbook.
If you need the formulas, then you can leave them as is. Of course if you can prepare your data so it's in a tabular format then that's always the safest and most efficient, but it's also easily fixed with Power Query.
Mynda
