PowerQuery, different headers splitting and merging

[jozik86]

Hi,


I'm having trouble with the task in PowerQuery. I am trying to merge several Excel files. Each file has more or less the same layout of columns. What I mean exactly is:


Month; A|no; B|no; C|no; D|no


Where A, B, C, D is a constant part of header in a files. However the other part after character "|" is a number. This number is different for each file. For example in 1st file:


Month; A|2222; B|1111; C|3333; D|4444
10-2001;100;200;300;400


in 2nd file:


Month; A|2223; B|1112; C|3334; D|4445
10-2001;10;20;30;40


I am trying to get a query load data into Excel from folder (many files) transformed to this layout:


Month; Name; Number; Value


So the result would be:


Month; Name; Number; Value
10-2001;A;2222;100
10-2001;B;1111;200
10-2001;C;3333;300
10-2001;D;4444;400
10-2001;A;2223;10
10-2001;B;1112;20
10-2001;C;3334;30
10-2001;D;4445;40


Any help would be much appreciated!

 

[MarcelBeug]

The following requires some background knowledge of Power Query.
If it doesn't make sense, let me know and I can clarify the steps with a video.

If you have the latest version of Power Query, then - if you read files from a folder - several query objects are automatically created when you choose "Combine Binaries".

One of those query objects is a query called "Transform Sample Binary from ...".

In this query you can define your transformations.

I adjusted the code as follows (the first 3 steps were already created automatically):

let
    Source = Excel.Workbook(#"Sample Binary Parameter1", null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Month"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns","Attribute",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute.1", "Name"}, {"Attribute.2", "Number"}})
in
    #"Renamed Columns"

Steps:
- Select the "Month" column,
- Choose "Unpivot" - "Unpivot Other Columns"
- Split Column By Delimiter (|) (the next step is created automatically)
- Rename the 2 new columns

As finishing touch you need to delete the last step in your main query (Changed Type) as this produces an error;
you may want to recreate this step with the correct column names.

 

[jozik86]

Thank you for the reply. Where do you select files in your query?
updated my PQ but still have the issue with - I believe the very first step as I cant find in your query path choice etc. I choose simply:
- From folder
- I point the path to the folder
- I choose Edit
When I choose Combine binaries I get the error.

 

[MarcelBeug]

The code in my previous post is just the code of the "Transform Sample Binary from ..." query.

Watch this video for all steps.

 

[jozik86]

Thank you very much!! This video helped a lot. I had troubles because I didn't have newest PQ (my O365 Pro Plus update channel was set to deferred) but finally managed do that.
Users with older PQ version won't be able to use the code - is that correct?

Thank you once again!

 

[MarcelBeug]

Maybe they can use the code, or at least the parts they require, i.e. the generated function and the main query. All other objects are not required to run the code.

One point of attention is that the function is somehow linked to the example transformation query, so that might prevent the code from being used in older versions.

I guess, if you create a new Excel workbook and copy the main query and the function code from the advanced editor into the new workbook (for each copy: first create a query via new query - Other - Blank Query, or similar steps, go to the advanced editor and paste the code) it should work with older versions as well.