Forum Discussion
lravikiran
Helper I
6 years agoConcatenate the top colomns
Hi Experts, So if you see in coloumn2-Total field needs to be Replace the Coloumn3 and Coloumn 4 null values. Similarly Coloumn6-B42136 should replace he coloumn7 and coloumn8 nulls...
- 6 years ago
Hi lravikiran
Add an index column from 1, then add M query in Advanced editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKskvScxR0lGCIyNDEyNjE92U8rSi1HKERKxOtFJickkpWHFRmjGQLEssykzMS06FqMAjCdKbDGQYgrCBgQGQsgSTJmASZkMsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [column2 = _t, column3 = _t, column4 = _t, column5 = _t, column6 = _t, column7 = _t, column8 = _t, column9 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"column2", type text}, {"column3", type text}, {"column4", type text}, {"column5", Int64.Type}, {"column6", type text}, {"column7", type text}, {"column8", type text}, {"column9", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Replaced Value_cl3"=Table.ReplaceValue(#"Added Index",each [column3],each if [Index]=1 and [column3]="" then [column2] else [column3],Replacer.ReplaceValue,{"column3"}), #"Replaced Value_cl4" =Table.ReplaceValue(#"Replaced Value_cl3",each [column4],each if [Index]=1 and [column4]="" then [column2] else [column4],Replacer.ReplaceValue,{"column4"}), #"Replaced Value_cl7" =Table.ReplaceValue(#"Replaced Value_cl4",each [column7],each if [Index]=1 and [column7]="" then [column6] else [column7],Replacer.ReplaceValue,{"column7"}), #"Replaced Value_cl8" =Table.ReplaceValue(#"Replaced Value_cl7",each [column8],each if [Index]=1 and [column8]="" then [column6] else [column8],Replacer.ReplaceValue,{"column8"}) in #"Replaced Value_cl8"Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-juanli-msft
Community Support
6 years agoHi lravikiran
Add an index column from 1, then add M query in Advanced editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKskvScxR0lGCIyNDEyNjE92U8rSi1HKERKxOtFJickkpWHFRmjGQLEssykzMS06FqMAjCdKbDGQYgrCBgQGQsgSTJmASZkMsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [column2 = _t, column3 = _t, column4 = _t, column5 = _t, column6 = _t, column7 = _t, column8 = _t, column9 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"column2", type text}, {"column3", type text}, {"column4", type text}, {"column5", Int64.Type}, {"column6", type text}, {"column7", type text}, {"column8", type text}, {"column9", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Replaced Value_cl3"=Table.ReplaceValue(#"Added Index",each [column3],each if [Index]=1 and [column3]="" then [column2] else [column3],Replacer.ReplaceValue,{"column3"}),
#"Replaced Value_cl4" =Table.ReplaceValue(#"Replaced Value_cl3",each [column4],each if [Index]=1 and [column4]="" then [column2] else [column4],Replacer.ReplaceValue,{"column4"}),
#"Replaced Value_cl7" =Table.ReplaceValue(#"Replaced Value_cl4",each [column7],each if [Index]=1 and [column7]="" then [column6] else [column7],Replacer.ReplaceValue,{"column7"}),
#"Replaced Value_cl8" =Table.ReplaceValue(#"Replaced Value_cl7",each [column8],each if [Index]=1 and [column8]="" then [column6] else [column8],Replacer.ReplaceValue,{"column8"})
in
#"Replaced Value_cl8"
Best Regards
Maggie
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.