Power Query Challenge 4

[Ken Puls]

Hi everyone,

Attached here you'll find the sample data for Power Query Challenge 4, as posted on the Excelguru blog. (Full details of the challenge and desired outputs are listed in the blog post.)

In order to submit your solution, please just reply to this thread, attach your workbook (use the Advanced Reply), and give us a brief description of how you approached the issue. (Did you use a custom function, make multiple queries, create parameters, drive it all through the user interface, etc...?)

We'd love to encourage discussion about the challenge and solutions provided. Please feel free to ask questions and give praise to the approaches provided by others.

 

[bigwest60]

My answer

I promoted headers then renamed columns using "Source{0}[Name]"

 

[Ken Puls]

Love it. That is slick and so much quicker than my solution!

 

[Duyarra]

Ok, I think I got it. I tried the insert index approach and it tirned out to be fine. That was fun and love the new format, thanks! bigwest60 solutions seems quite faster though. Challenge 4_DU.xlsx

 

[Rudi]

I see I took the exact same route as "bigwest60".

After promoting headers, I edited the Table.Rename function to point to the first cell in Source.

View attachment Challenge 4.xlsx

TX for the challenge Ken.
(Love it!)

 

[fshaikh]

Transform with M-Code Custom Function

Hi Ken..

Please see my PQ Challenge-4, I try to do it by making a customs function.. called as fnTransform(Source) and that will transform my table as we need

thanks a lot for posting the challenge and we just love doing it

Regards, Faraz Shaikh

 

[Ken Puls]

Hey everyone,

Even though I think the bigwest approach is better, here's how I put my solution together. Way more steps to get to the same end result!

I've fully detailed how I worked through my solution on the blog.

 

[fshaikh]

Omg..!! that was super awesome.. :cheer2:
never thought of doing demoting headers..
Thanks for sharing your solution..!!!

Regards, Faraz Shaikh

 

[Ron Coderre]

I assumed that the source table's first column might not be "Name", so I referenced the first column by position and renamed it:

let
    Source = Admin,
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, "Name"}})
in
    #"Renamed Columns"

 

[Kolyu]

Hi guys,

That is an interesting challenge, thank you Ken!

My solution might be a bit strange and off-topic, but I'm submitting it anyway.
First of all I have to apologize that I changed the data. But I am so fed up with this accounting and reporting stuff, so I decided to play with something more pleasant, i.e. soccer.


Small clarification for those of you who are not familiar with soccer. Generally in each country there are 2 types of competitions - a League and a Cup.
There are two major international competitions on club level: Champions League and Europa League in Europe and Copa Libertadores and Copa Sudamericana in South America. Up until 2004 the winners in Champions League and Copa Libertadores played a match for Intercontinental Cup.

So I replaced accounting tables with some stats for some of leading clubs in 8 countries in Europe and S. America. As you see I made my life harder because the tables have different number of columns.
And I went a step further - I decided to made it user friendly, i.e. not to go and fix anything in Power Query Editor.

Long story, short, here is a picture of my queries:



8 for the qountries, 3 working and 1 that loads the final result in Excel

query '1 Countries List' creates a dynamic list of countries that is used for creating drop-down menu with Data Validation.



The drop-down menu is in cell A2 below and in fact A1:A2 is a table that I loaded in Power Query and drilled down to create the parameter 'Filter'.

My third working query appends tables for all countries, then filters column 'Country' using the filter above.

Finally I reference that query, transpose the table couple of times, and use the super cool idea of bigwest60 to promote headers and then rename columns using "Source{0}[Name]"

Here is the result:


Not the most elegant solution but works

Here is the file:
View attachment Challenge 4 - Kolyu's Solution.xlsx

 

[hossat]

Hi,
Thank you very much for all the cool challenges.
Hereafter a solution with only 2 steps (in french version of Excel 2016) :

let
    Source = Sales,
    #"En-têtes promus" = Table.PromoteHeaders(Source),
    #"Colonnes renommées" = Table.RenameColumns(#"En-têtes promus",{{Table.ColumnNames(#"En-têtes promus"){0}, "Name"}})
in
    #"Colonnes renommées"

View attachment Challenge 4 in 1 step in PQ FR.xlsx

 

[Bill Szysz]

That's my two cents.

fxBillSzysz

(t as table, optional n as nullable number) =>
    let
        Num = if n = null then 1 else n,
        #"Promoted Headers" = Table.PromoteHeaders(t, [PromoteAllScalars=true]),
        ColumnNames = Table.ColumnNames(#"Promoted Headers"),
        tFirstNColumnNames = List.FirstN(Table.ColumnNames(t), Num),
        OldNewHeaders = List.Zip({ColumnNames, tFirstNColumnNames & List.Skip(ColumnNames, Num)}),
        Result = Table.RenameColumns(#"Promoted Headers", OldNewHeaders)
    in
        Result

Invoking function for "Admin: table

let
    Source = fxBillSzysz(Admin, null)
in
    Source

Invoking function for "Department" table (keep 2 headers frm the left side of the "Department" table)

let
    Source = fxBillSzysz(Department, 2)
in
    Source

 

[Ken Puls]

Great stuff everyone!

 

[happymedia]

Hi Ken,
Thank you for the challenge.
To get the solution rather than renaming the first column using its name I used the Table.ColumnNames(Source){0} to refer to it using its position.

 

[Tooley]

Hi Guys,

Assuming "Name" would already be an existing column name, I would have gone with the following which allows flexibility in it's index position (though I think I'll use BigWest's solution in future as has less code and is simpler to read :thumb::thumb: ):

let
    Source = Admin,
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns( #"Promoted Headers",{ { Table.ColumnNames( #"Promoted Headers" ){ List.PositionOf( Table.ColumnNames(Source) , "Name") }, "Name"}}) 
in
    #"Renamed Columns"

 

[Ayrahvon]

A little late to the party (and finally decided to make an account), but my solution was a little less elegant the the others, but haven't seen it posted yet.

let
    Source = Sales,
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Replaced Value" = Table.ReplaceValue(#"Demoted Headers","Column1",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Column2",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Column3",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Column4",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Filled Up" = Table.FillUp(#"Replaced Value3",{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Up"),
    #"Removed Top Rows" = Table.Skip(#"Promoted Headers",1)
in
    #"Removed Top Rows"

Like Ken, I started with demoting the headers, but my goal was to get to fill up. I really thought that there was a way to wildcard replaced value so you could so something along the lines of Column* and replace all of them in one go, but since it was only 4 columns I opted to continue replacing each. It works for this example, but I'd hardly want to use it on a larger data set.