Renaming column conditionally and shifting columns

[Anonymous]

Hi everyone, 

I am dealing with a situation and get stuck in this step. I divide my problem into 2 sub-problems.

1st sub-problem: as you can see in the first table below, there are columns without a header after a normal column. I want to add a new header for these empty-head-columns, which equals to [header of previous column]+"in Text". E.x: the second column will be "Column1 in Text" and so on, as you can see in the Expectation table. 

2nd sub-problem: this data does not have headers on the same row (which are in column5, 6, 7). Therefore, I have tried to find a solution as IF RowIndex=1 & [ColumnX]="" THEN [ColumnX] = [ColumnX] WHERE RowIndex=0. Very sorry if my syntax is unprofessional. 

Any help from you would be so valuable to me!

Thank you and wish you a nice week.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

We can work it out in power query like that.

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3NM1TSUQIiCMcYwgGhWJ1oJZCcERCDhE2A2BSIzcAy5kCWBRBbArGhAYgAqTU0UoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", Int64.Type}, {"(blank).2", type text}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Added Conditional Column" = Table.AddColumn(#"Transposed Table", "Custom", each if Text.Contains([Column1], "(blank)") then [Column2] else [Column1]),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1", "Column2"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns"),
    #"Reversed Rows" = Table.ReverseRows(#"Transposed Table1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"Column1_1", Int64.Type}, {"Column3", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}})
in
    #"Changed Type2"

[Anonymous]

@v-frfei-msft thank you so much! I found Table.FillDown() very useful.