Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ahmadhlawa
Regular Visitor

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"

combined view 2.JPG

 

 

10 REPLIES 10
v-kelly-msft
Community Support
Community Support

Hi @ahmadhlawa ,

 

For combining files,you can use "append",here is a reference.

https://www.poweredsolutions.co/2019/04/09/combine-or-append-data-in-power-bi-power-query-main-conce...

 

For "unpivot",you can refer to:

https://radacad.com/pivot-and-unpivot-with-power-bi

 

 
Best Regards,
Kelly
Did 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.

camargos88
Community Champion
Community Champion

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.

Capture.PNG

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.

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@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).

 

 trial.JPG

 

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).



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88  your file looks fine, but i don't have the sources on my PC, so can't trace the steps.

 

do you do those steps to the sample file (from your screen shot i can't see the "Source name" column)?

what i tried is, first i combine all files, then unpivot them, then put the functions you mentioned.

 

if you do it to the sample file can you please elaborate how can i do that?

@ahmadhlawa ,

 

Get the files from here: Download Files 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88  sorry for bothering alot but you are really helping.

now i applied all the steps until pivoting on the sample file, when i click apply and close for the query, it gives me the error as "column "column3" of the table wasn't found"

@ahmadhlawa ,

 

I just refreshed the pbix without erros.

Did you change something on the code or excel files ? If yes, maybe it needs some adjustments.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88  thanks a million, a big fat thank you 🙂

i was working on another file which had lots of other equations for the aggregrate query, that was an error from there, removed the step of modifying "column3" then it worked.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.