Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a heading shift as in the picture. I want to aligh them to the same row so I can promote it as heading column. I try filldown but doesn't work because I only want to fill down 1 row. Then I try fill up. it is also filled from the bottom where where is null. How could I re-aligh this heading to the same row.
Thanks,
Solved! Go to Solution.
let
Source = Excel.Workbook(Web.Contents("https://xxxxxx.xlsx"), null, true),
#"Partner Advance Liquidatio_Sheet" = Source{[Item="Partner Advance Liquidatio",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Partner Advance Liquidatio_Sheet",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}, {"Column20", Int64.Type}, {"Column21", Int64.Type}, {"Column22", Int64.Type}, {"Column23", Int64.Type}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", Int64.Type}, {"Column28", Int64.Type}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", Int64.Type}, {"Column32", type any}, {"Column33", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",6),
NewStep=let a=Table.ToRows(#"Removed Top Rows") in Table.PromoteHeaders(Table.FromRows({List.Transform(List.Zip(List.FirstN(a,2)),each Text.Format("#{0}#{1}",_))}&List.Skip(a,2)))
in
NewStep
Could you add the code inside. I am not sure where I need to change.
let
Source = Excel.Workbook(Web.Contents("https://xxxxxx.xlsx"), null, true),
#"Partner Advance Liquidatio_Sheet" = Source{[Item="Partner Advance Liquidatio",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Partner Advance Liquidatio_Sheet",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}, {"Column20", Int64.Type}, {"Column21", Int64.Type}, {"Column22", Int64.Type}, {"Column23", Int64.Type}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", Int64.Type}, {"Column28", Int64.Type}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", Int64.Type}, {"Column32", type any}, {"Column33", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",6)
in
#"Removed Top Rows"
let
Source = Excel.Workbook(Web.Contents("https://xxxxxx.xlsx"), null, true),
#"Partner Advance Liquidatio_Sheet" = Source{[Item="Partner Advance Liquidatio",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Partner Advance Liquidatio_Sheet",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}, {"Column20", Int64.Type}, {"Column21", Int64.Type}, {"Column22", Int64.Type}, {"Column23", Int64.Type}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", Int64.Type}, {"Column28", Int64.Type}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", Int64.Type}, {"Column32", type any}, {"Column33", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",6),
NewStep=let a=Table.ToRows(#"Removed Top Rows") in #table(List.Transform(List.Zip(List.FirstN(a,2)),each Text.Format("#{0}#{1}",_)),List.Skip(a,2))
in
NewStep
it give me an error.
I share the Power BI file example. What I want is the promote the below in red box to be column header. Here is the link to pbix file
at least you have two columns that both value in the first two rows are null
So how could I fix it?
let
Source = Excel.Workbook(Web.Contents("https://xxxxxx.xlsx"), null, true),
#"Partner Advance Liquidatio_Sheet" = Source{[Item="Partner Advance Liquidatio",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Partner Advance Liquidatio_Sheet",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}, {"Column20", Int64.Type}, {"Column21", Int64.Type}, {"Column22", Int64.Type}, {"Column23", Int64.Type}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", Int64.Type}, {"Column28", Int64.Type}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", Int64.Type}, {"Column32", type any}, {"Column33", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",6),
NewStep=let a=Table.ToRows(#"Removed Top Rows") in Table.PromoteHeaders(Table.FromRows({List.Transform(List.Zip(List.FirstN(a,2)),each Text.Format("#{0}#{1}",_))}&List.Skip(a,2)))
in
NewStep
=let a=Table.ToRows(PreviousStepName) in #table(List.Transform(List.Zip(List.FirstN(a,2)),each Text.Format("#{0}#{1}",_)),List.Skip(a,2))
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
42 | |
26 | |
14 | |
14 | |
12 |