Forum Discussion
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 is an example:
In this exammple, I have everything to "Target" and the left correctly promoted as a header, but my columns to the right are showing as "Column9" when I need it to show the month name. I am trying to promote the columns only to the right of Target.
I'm not well versed in Power Query language, but I attempted to do this by modifying the code for the existing row promotions and changing it to only be a single column.
let
// Previous steps...
Source = YourPreviousStepHere,
PromoteHeaderOfSingleColumn = Table.TransformColumns(Source, {"Column9", each _, type table}),
ExpandedColumn = Table.ExpandTableColumn(PromoteHeaderOfSingleColumn, "Column9", {"Jan"})
in ExpandedColumn
Any thoughts on if this is possible?
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"
4 Replies
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/1447523Hello 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"
