Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Power Query Challenge 4

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,304
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider

    Power Query Challenge 4



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #2
    Neophyte bigwest60's Avatar
    Join Date
    Nov 2018
    Posts
    1
    Articles
    0
    Excel Version
    Office 365

    My answer

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

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,304
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Love it. That is slick and so much quicker than my solution!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Neophyte Duyarra's Avatar
    Join Date
    Nov 2018
    Posts
    1
    Articles
    0
    Excel Version
    365
    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

  5. #5
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    132
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    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.

    Challenge 4.xlsx

    TX for the challenge Ken.
    (Love it!)
    Regards,
    Rudi
    www.eileenslounge.com

  6. #6
    Neophyte fshaikh's Avatar
    Join Date
    Nov 2018
    Posts
    2
    Articles
    0
    Excel Version
    Office365

    Post 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
    Attached Files Attached Files

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,304
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Neophyte fshaikh's Avatar
    Join Date
    Nov 2018
    Posts
    2
    Articles
    0
    Excel Version
    Office365
    Omg..!! that was super awesome..
    never thought of doing demoting headers..
    Thanks for sharing your solution..!!!

    Regards, Faraz Shaikh

  9. #9
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    84
    Articles
    0
    Excel Version
    2013, 2016, O365
    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"
    Attached Files Attached Files
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  10. #10
    Neophyte Kolyu's Avatar
    Join Date
    Oct 2018
    Location
    Sofia, Bulgaria
    Posts
    1
    Articles
    0
    Excel Version
    2007, 2010, 2013, 2016
    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.
    Click image for larger version. 

Name:	PQ ch.4 01.png 
Views:	258 
Size:	27.7 KB 
ID:	8656

    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:

    Click image for larger version. 

Name:	PQ ch.4 02.png 
Views:	259 
Size:	6.1 KB 
ID:	8657

    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.

    Click image for larger version. 

Name:	PQ ch.4 03.png 
Views:	259 
Size:	5.8 KB 
ID:	8658

    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:
    Click image for larger version. 

Name:	PQ ch.4 04.png 
Views:	259 
Size:	14.8 KB 
ID:	8659

    Not the most elegant solution but works

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

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •