Results 1 to 3 of 3

Thread: Expanding expanding data...!

  1. #1
    Seeker Martinl's Avatar
    Join Date
    Dec 2016
    Posts
    13
    Articles
    0
    Excel Version
    2016 MSO (16.0.9126.2259)

    Expanding expanding data...!



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

    I get an awful monthly file like this called Monthly Sales of Widgets.xlsx which I need to work with.

    Month 1
    Product ID Jan-16
    Widget1 122
    Widget2 2444

    Month 2
    Product ID Jan-16 Feb-16
    Widget1 122 102
    Widget2 2444 2001

    Month 3 etc
    Product ID Jan-16 Feb-16 Mar-16
    Widget1 122 102 98
    Widget2 2444 2001 3021

    How do you get Power Query to import this file with the additional columns each month without having to go in and manually adjusting the columns expanded.

  2. #2
    Acolyte Comfy's Avatar
    Join Date
    Oct 2016
    Posts
    61
    Articles
    0
    Excel Version
    2019
    Can you post your M?

    I would Import the File.
    Use "Unpivot Other Columns"

    Table1

    Product ID Jan-16 Feb-16
    Widget1 122 102
    Widget2 2444 2001

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Product ID"}, "Attribute", "Value"),
        #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Month"}})
    in
        #"Renamed Columns"
    As long as the table contains a column called [Product ID] it will run and include any additional columns.

  3. #3
    [QUOTE=Comfy;29065]
    I would Import the File.
    Use "Unpivot Other Columns"[QUOTE]

    Awesome, now I feel so dim...

Posting Permissions

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