Forum Discussion
Synik_PBI
Helper II
Helper IIPromote 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...
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_PBIHelper II
Helper IIHello lbendlin
I have uploaded my file to Dropbox. This is an example file of how the worksheet is setup.
https://www.dropbox.com/scl/fi/jsi9xy3dzw0rsrlnukhel/Example-SLA-Data.xlsx?rlkey=qm7sdcqn4cstytrh8wcb0b1i7&st=njngieq5&dl=0
The expected output should look something like a PBI-friendly format like this:
where all dark blue bars is consolidated into 1 header with the data broken out below like a database.
lbendlin
Super User
Super UserThank 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"