none
How to format different monthly tabs into one pivotable format RRS feed

  • Question

  • Hello. I have monthly financial results with metrics along the rows and distinct products across the columns. 

    I would like to easily format this information so that I can create pivots and pivot charts based on the information. 

    Does power query have a way of making that easier? 


    • Edited by Albo44 Monday, April 29, 2019 6:33 AM
    Monday, April 29, 2019 6:31 AM

Answers

  • It depends of shape of your data. If it is same for each sheet, you may convert the code above to function (with adding month column):

    //fn
    
    (YourSheet) =>
    
    let
        Source = Excel.Workbook(File.Contents("C:\path\YourWorkbook.xlsx"),null, true),
        Sheet1_Sheet = Source{[Item=YourSheet,Kind="Sheet"]}[Data],
        removeRows = Table.Range(Sheet1_Sheet, 4, 13),
        removeColumns = Table.SelectColumns(removeRows,List.FirstN(Table.ColumnNames(removeRows),9)),
        headers = Table.PromoteHeaders(removeColumns, [PromoteAllScalars=true]),
        unpivot = Table.UnpivotOtherColumns(headers, {"Column1"}, "Metrics", "Value"),
        add = Table.AddColumn(unpivot, "month", each YourSheet)
    in
        add

    Then apply this function to list of months:

    let
        months = List.Transform({1..12}, (x)=>Date.MonthName(#date(2019,x,1), "en-US")),
        applyFn = List.Transform(List.FirstN(months,4), fn),
        combine = Table.Combine(applyFn)
    in
        combine
    Monday, April 29, 2019 12:14 PM

All replies

  • Hi Albo44,

    Yes, Power Query is proper tool for this task. Code you need (for single sheet) should be like this:

    let
        Source = Excel.Workbook(File.Contents("C:\path\YourWorkbook.xlsx"),null, true),
        Sheet1_Sheet = Source{[Item="YourSheet",Kind="Sheet"]}[Data],
        removeRows = Table.Range(Sheet1_Sheet, 4, 13),
        removeColumns = Table.SelectColumns(removeRows,List.FirstN(Table.ColumnNames(removeRows),9)),
        headers = Table.PromoteHeaders(removeColumns, [PromoteAllScalars=true]),
        unpivot = Table.UnpivotOtherColumns(headers, {"Column1"}, "Metrics", "Value")
    in
        unpivot

    On step removeColumns you should specify number of columns, you want to keep (instead 9). Or you may remove this step, if there is no data to the right of your data range.

    Monday, April 29, 2019 8:38 AM
  • Ok thanks. And how can I do this for each sheet? 
    Monday, April 29, 2019 9:24 AM
  • It depends of shape of your data. If it is same for each sheet, you may convert the code above to function (with adding month column):

    //fn
    
    (YourSheet) =>
    
    let
        Source = Excel.Workbook(File.Contents("C:\path\YourWorkbook.xlsx"),null, true),
        Sheet1_Sheet = Source{[Item=YourSheet,Kind="Sheet"]}[Data],
        removeRows = Table.Range(Sheet1_Sheet, 4, 13),
        removeColumns = Table.SelectColumns(removeRows,List.FirstN(Table.ColumnNames(removeRows),9)),
        headers = Table.PromoteHeaders(removeColumns, [PromoteAllScalars=true]),
        unpivot = Table.UnpivotOtherColumns(headers, {"Column1"}, "Metrics", "Value"),
        add = Table.AddColumn(unpivot, "month", each YourSheet)
    in
        add

    Then apply this function to list of months:

    let
        months = List.Transform({1..12}, (x)=>Date.MonthName(#date(2019,x,1), "en-US")),
        applyFn = List.Transform(List.FirstN(months,4), fn),
        combine = Table.Combine(applyFn)
    in
        combine
    Monday, April 29, 2019 12:14 PM