Hi all,
I am trying to upload several files with data from different years in PQ. These files have dates (DD/MM/YY) as headers which need to be unpivoted in order to have a single column named "dates".
The process I follow in PowerBI is: upload a folder, combine and transform the files in the folder, clean the first file in PQ using "transform sample file".
After unpivotting the dates in the first file however I have a problem with the other documents which have different dates belonging to different financial years. As a result I am not able to combine the data between those files in fact only the data from the first one is successfully uploaded in powerbi.
All the other files would show an error because after unpivotting the dates in the first report PQ would be looking for the same dates in the other files too.
Is there a way to fix this issue or to get around it? What I'm trying to achieve is to combine the data of several financial years in order to analyse and compare that data in powerBI.
Thanks a lot for your help
I supect that you are unpivotting by selecting the date columns and then Unpivot Selected Columns. If so, change the process by selecting the non-date columns and then Unpivot Other Columns. That should solve it
Hi Riny,
I tried to do as you suggested but I still get an error message.
In fact, I have tried to follow the same process by creating a new folder with two files using data from the same 12 months period ( The two files are named actuals and budget) but I still get an error.
If I click on the "invoke custom function" in PQ the error I shown for the budget file is the following:
An error occurred in the ‘Transform File (2)’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Item=Actuals
Kind=Sheet
Table=
Difficult to diagnose without seeing the files. Can you upload two files with some mocked-up data but leaving the file structures exactly as they are in your real file?
I seem to have found what the problem was. I had to change the name of the budget sheet into 'actuals' (same name as the actuals file sheet) in order to get rid of the problem.
It's not ideal though cause now I have actuals and budget data merged together and the only way to filter them out in powerBI is to use the 'source.name' field which is a field that I would normally delete in the power query.
does anyone know another way to help me separate the actuals number from the budget ones so that i can compare them more easily?
Thanks
Give the sheets a neutral name like "Data" and add one column to each sheet/table, for instance "Category" where you enter Actual or Budget on each row. Then use that column to separate Actuals from Budget.
Thanks Riny, I did something like that however when I move to the 'other query' from 'transform sample file' the date column shows null for the budget file. Below what I did
In the original files I named the dates columns Actuals sep-20, Actuals Oct-20 etc ( same thing happened in the budget file)
In PQ I cleaned the data so to have a column for Actuals and Budget and one for the dates
Since I used the 'actuals file' in the sample file field when I move to the 'other query' field I see only null values for dates in budget.
Therefore I was trying to check if i could replace the null values with dates by using the 'replace values' in the ribbon however if I were to do so I would come up with twelve 01/09/2020 ( first month of the fiscal year) and I don't want that . I would like to know whether there is a way to manually change the formula in the replaced value step in order to fix this issue or whether there is a even easier way to do that.
Thanks
Please upload some files. It's still difficult to follow what you're doing based on text alone.
