iterate Table.ReplaceValue based on relative column reference

[Anonymous]

Hello,

I currently have a large dimensional dataset that is formatted like:

A	null	null	B	null	null
S1	S2	S3	S1	S2	S3
data	data	data	data	data	data

I'm trying to transform it into the below so I can concatenate and make headers from the top 2 rows.

A	A	A	B	B	B
S1	S2	S3	S1	S2	S3
data	data	data	data	data	data

Since it's so high dimensional I'd like to do this programatically, my first inclination is to do something like: Table.ReplaceValue(#"Result", null, x , Replacer.ReplaceValue, {Headers}), with x being a relative column reference to either col -1 or col - 2. Is there a way to do this?

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

You could try this way as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUcorzclBUE5oIrE60UrBhkBesBGIMAYRyFyQfEpiSSKQR5CKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Replaced Value" = Table.ReplaceValue(#"Transposed Table","null",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Transposed Table1" = Table.Transpose(#"Filled Down")
in
    #"Transposed Table1"

or

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUcorzclBUE5oIrE60UrBhkBesBGIMAYRyFyQfEpiSSKQR5CKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t, ColumnC = _t, ColumnD = _t, ColumnE = _t, ColumnF = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnA", type text}, {"ColumnB", type text}, {"ColumnC", type text}, {"ColumnD", type text}, {"ColumnE", type text}, {"ColumnF", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Replaced Value" = Table.ReplaceValue(#"Transposed Table","null",null,Replacer.ReplaceValue,{"Column2"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column2"}),
    #"Transposed Table1" = Table.Transpose(#"Filled Down"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"ColumnA", type text}, {"ColumnB", type text}, {"ColumnC", type text}, {"ColumnD", type text}, {"ColumnE", type text}, {"ColumnF", type text}})
in
    #"Changed Type2"

Result:

and here is sample pbix file, please try it.

[Anonymous]

Thanks, that worked!