Forum Discussion
Promote first row to header on a single column?
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"
Looks like you first want to unpvot your data.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hello 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.
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"Thanks, I'm glad to get a 2nd opinion on it being unusable!