Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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



Community Support
Community Support

Hi @ahmadhlawa ,


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


For "unpivot",you can refer to:


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

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.


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




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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors