Power Query Challenge 4

Ken Puls

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,531
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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. :)
 

Attachments

  • Challenge 4.xlsx
    19.5 KB · Views: 169
My answer

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

Attachments

  • Challenge 4.xlsx.xls
    20.6 KB · Views: 137
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
 
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 :)
 

Attachments

  • Challenge 4 - Faraz Shaikh, fshaikh Excel Master.xlsx
    20.4 KB · Views: 34
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.
 

Attachments

  • Challenge 4 - Ken's Solution.xlsx
    20.9 KB · Views: 74
Omg..!! that was super awesome.. :cheer2:
never thought of doing demoting headers..
Thanks for sharing your solution..!!!

Regards, Faraz Shaikh
 
I assumed that the source table's first column might not be "Name", so I referenced the first column by position and renamed it:
Code: 
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"
 

Attachments

  • Challenge 4_RonC.xlsx
    20.1 KB · Views: 17
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.
PQ ch.4 01.png

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:

PQ ch.4 02.png

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.

PQ ch.4 03.png

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:
PQ ch.4 04.png

Not the most elegant solution but works

Here is the file:
View attachment Challenge 4 - Kolyu's Solution.xlsx
 
Hi,
Thank you very much for all the cool challenges.
Hereafter a solution with only 2 steps (in french version of Excel 2016) :

Code: 
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
 
That's my two cents.

fxBillSzysz
Code: 
(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

Code: 
let
    Source = fxBillSzysz(Admin, null)
in
    Source

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

Code: 
let
    Source = fxBillSzysz(Department, 2)
in
    Source
 

Attachments

  • Challenge_4_BillSzysz.xlsx
    25.1 KB · Views: 22
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.
 
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: ):

Code: 
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"
 

Attachments

  • Challenge 4.xlsx
    20.2 KB · Views: 8
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.

Code: 
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.
 
You must log in or register to reply here.
Back
Top