Load& Combine + Unpivot

[ahmadhlawa]

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"

[v-kelly-msft]

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!

[ahmadhlawa]

@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]

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.

[ahmadhlawa]

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

 

[camargos88]

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

[ahmadhlawa]

@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?

[camargos88]

@ahmadhlawa ,

Get the files from here: Download Files 

[ahmadhlawa]

@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"

[camargos88]

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

[ahmadhlawa]

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