Forum Discussion
Load& Combine + Unpivot
hi Team,
i have monthly excel files that contains a forecast for some items, each item has a code and descrition.
the table i recieve monthly contain first column called "code", second column called "description", then 6 more columns which are named by 1st of the month
i.e. if we are in April then the 1st column is called 1-4-2020, then second column called 1-5-2020,...until the 6th column which is to be called 1-9-2020.
then next month when we are in May then the 1st column is called 1-5-2020,...and last column i called 1-10-2020 ; attached picture.
i need to combine those monthly files together, view like attached picture.
then i need to unpivot all columns except the "Code" and "Description"
10 Replies
Hi ahmadhlawa ,
Check this file: Download PBIX
I've imported the data using FOLDER as connector, edit the Transform Sample File:
1 -> Unpivoting the data
2-> This code allocates the dates in the correct order.
3 -> Filter out the Column1 leaving the CODE values out.
4 -> Remove the Attribute column.
The last step is to pivot the dates using the values and don't aggregate them.
I hope it helps.
camargos88 thanks but it gives me the below result. for each source
however, souce 1 contains values from 1 May20 till 1 Mar21, and source 2 contains values from 1 June20 till 1 April21.
kindly referring again to the combined view in my post as the required output (red cells means month column should apear with a zero value).
Hi ahmadhlawa ,
Have you checked the file ?
I've created this scenario with 3 files, when it doesn't have dates, it's null (can be replaced to 0).
Hi ahmadhlawa ,
For combining files,you can use "append",here is a reference.
For "unpivot",you can refer to:
https://radacad.com/pivot-and-unpivot-with-power-bi
Best Regards,
KellyDid I answer your question? Mark my post as a solution!v-kelly-msft i'm loading files from a sharepoint folder, and each month i recieve a new file in the same folder.
i'm afraid its not practical to do this process each month.
