Forum Discussion
iterate Table.ReplaceValue based on relative column reference
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?
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.
2 Replies
v-lili6-msftCommunity Support
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.