Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
manoplaeli
Helper II
Helper II

Fill up only top row

Screenshot_3.png

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,

1 ACCEPTED 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

View solution in original post

7 REPLIES 7
manoplaeli
Helper II
Helper II

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.

Screenshot_4.png

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

Screenshot_5.png

 

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
wdx223_Daniel
Super User
Super User

=let a=Table.ToRows(PreviousStepName) in #table(List.Transform(List.Zip(List.FirstN(a,2)),each Text.Format("#{0}#{1}",_)),List.Skip(a,2))

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors