Forum Discussion
Synik_PBI
2 years agoHelper II
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...
- 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"
lbendlin
2 years agoSuper User
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"
Synik_PBI
2 years agoHelper II
Thanks, I'm glad to get a 2nd opinion on it being unusable!