Forum Discussion

Synik_PBI's avatar
Synik_PBI
Helper II
2 years ago
Solved

Promote first row to header on a single column?

Hello! I'm doing some transformations on a very disorganized data set. I've run into a problem where I need to promote the first row to headers on only certain columns, not the entire dataset. Here i...
  • lbendlin's avatar
    lbendlin
    2 years ago

    Thank you for including a sample file. It is utterly unusable for Power BI. The crooked header is the least of your problems.

     

    Remove the comments rows, and fill down rows as appropriate. 

     

    Something like this

     

     

    let
        Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\Example SLA Data.xlsx"), null, true),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        #"Removed Top Rows" = Table.Skip(Sheet1_Sheet,4),
        #"Filled Down" = Table.FillDown(#"Removed Top Rows",{"Column1", "Column2"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column3] <> null and [Column3] <> "Comments")),
        #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SOW ", Int64.Type}, {"Business Owner", type text}, {"Metric Name/Description", type text}, {"Metric Measurement", type text}, {"Baselining", type text}, {"Weighting Factor", type number}, {"Target", type any}, {"Monthly Results", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Monthly Results", "Jan"}, {"Column9", "Feb"}, {"Column10", "Mar"}, {"Column11", "Apr"}, {"Column12", "May"}, {"Column13", "Jun"}, {"Column14", "Jul"}, {"Column15", "Aug"}, {"Column16", "Sep"}, {"Column17", "Oct"}, {"Column18", "Nov"}, {"Column19", "Dec"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column20"}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"SOW ", "Business Owner", "Metric Name/Description", "Metric Measurement", "Baselining", "Weighting Factor", "Target"}, "Month", "Value"),
        #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Target] <> "Expected"))
    in
        #"Filtered Rows1"