combine files with different (month) columns in power query

[Anonymous]

hi,

i'm trying to create a function in power query (for reuse for all other years) which is based on a typical accounting file that has, among others, 12 columns for different months of the year. the 1st row containing the months in a year will be promoted as headers & subsequently unpivoted.

however, i have a problem when i tried adding new file(s) with a resultant error message stating that it (the function) couldn't find/use the previous column names (e.g. 01/06/2011, 01/07/2011) since the subsequent new file has different column names for its months e.g. 01/06/2012, 01/07/2012.

any solution to this, pls?

tks, -nik

[amitchandak · Accepted solution]

@Anonymous , not very clear. Can you share a sample.

First, you should unpivot and correct column name

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

and then merge

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

For your case, I would suggest you you get data from folder, see details as below:

https://www.myonlinetraininghub.com/power-query-get-files-from-a-folder

https://exceloffthegrid.com/power-query-import-all-files-in-a-folder/

Regards,

Lin

[Anonymous]

thanks for your suggestion, @v-lili6-msft.

i'm still having problem to get all xlsx files from a folder into power bi. this is mainly bcoz each of the accounting files has different column names to reflect months for different years (e.g. file01 wil have columns jan10~dec10, whilst file02 wil have columns jan11~dec11). at the moment, i'm only able to import 1 file at a time & do the necessary data unstacking/transformation into tabular form for subsequent appending of all files into 1 file for all the years. 

i wish there's a way to handle the getting+combining+transforming all the accouting files with different column names in a more efficient way via power query to avoid the lengthy data sourcing / transformation. following video shows how a reusable function is created (but for variable filename, not for variable column name):

     https://youtu.be/3GIz50pftZ0

krgds, -nik

[amitchandak · Accepted solution]

@Anonymous , not very clear. Can you share a sample.

First, you should unpivot and correct column name

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

and then merge

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Anonymous]

hi again @amitchandak ,

the sample files are in a zipped file (filename: bs.org.01-02.2010-2011.zip) here 

   https://tinyurl.com/yyvbudz8

they are for 2 orgs (org01, org02) for 2 yrs (2010~2011 years).

to recap/clarify, i'm trying to create a function in power query that will allow me to do a repeated process for file combining operation when i upload accounting file(s) periodically (on monthly / quarterly/ annual basis). each attached files has different column names for the months for different years as metioned below. i need to find a solution to enable file combining operation based on that situation (changing/differen column names).

tks & krgds, -nik