Forum Discussion
Help building a report
I'm a beginner with using PowerBI and have very little experience. What is the easiest way to transform data in an Excel document that has names of expenses and months as the columns into something that is useable in PowerBI and also able to be refreshed each month to add in new monthly data? I put a small screenshot below as an example. There are close to one hundred rows of expenses.
As a workaround, I created a report by transposing the data in Excel to have months as the rows and the expenses as columns prior to importing it into Power BI. This worked for a historical snapshot, but not sure how to make it easy to refresh with new data going forward without having to do that same thing each month.
On a separate issue, the spreadsheet also comes in with lots of totals and subtotals that I just manually deleted before importing into PowerBI. Is there a best practice for that so I dont have to delete those each month too? Thanks in advance for any help!
You are going to want to spend some time learning Power Query. It will greatly help transforming your data.
If you have data that looks similar to the following loaded into Power BI you can use the Transform Data feature to access Power Query
It should look something like this in Power Query
I would first deselect the subtotals and total row from the account no column
Which should get you just the "data" rows
I would get rid of the total column ( you can create a DAX measure in Power BI later)
Now you have
Now you can use "Unpivot Other Columns" from the Transform tab, selecting the first two rows of the table
Now you have
Rename the "Attribute" column and change it to date type. change the value column to decimal number type
You can now Close & Apply this table to load it into Power BI
You can now create visuals like
This is just a very quick runthrough of what you could use Powe Query for. Power Query itself is extremely powerful. There are a lot of great resources online to help you out.
Hope this helps.
2 Replies
You are going to want to spend some time learning Power Query. It will greatly help transforming your data.
If you have data that looks similar to the following loaded into Power BI you can use the Transform Data feature to access Power Query
It should look something like this in Power Query
I would first deselect the subtotals and total row from the account no column
Which should get you just the "data" rows
I would get rid of the total column ( you can create a DAX measure in Power BI later)
Now you have
Now you can use "Unpivot Other Columns" from the Transform tab, selecting the first two rows of the table
Now you have
Rename the "Attribute" column and change it to date type. change the value column to decimal number type
You can now Close & Apply this table to load it into Power BI
You can now create visuals like
This is just a very quick runthrough of what you could use Powe Query for. Power Query itself is extremely powerful. There are a lot of great resources online to help you out.
Hope this helps.
Thank you! I will try this out. I appreciate your response!
