Concatenate the top colomns

[lravikiran]

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 so that they allign on top of the RF# and Variance too.

Please tell me solution.

REgards,

Ravi

[v-juanli-msft · Accepted solution]

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
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.

[Anonymous]

Is your requirement is to replace null values in columns 2 and 3 with the value from column 1? Similarly, do you want to replace null values in columns 7 and 8 with column 6? If so, then highlight columns 2 and 3 and choose replace values. Type in null for value to find and anytexthere for replace with .

Now, in the resulting M code formula bar at the top change "anytexthere" to each [Column1:

= Table.ReplaceValue(#"Changed Type",null,"anytexthere",Replacer.ReplaceValue,{"Column2", "Column3"})

to

= Table.ReplaceValue(#"Changed Type",null,each [Column1],Replacer.ReplaceValue,{"Column2", "Column3"})

Regards,

Mike

[Mariusz]

Hi @lravikiran 

Sorry, but I'm struggling to understand your requirement.

Can you include in your post an expected outcome?

Best Regards,
Mariusz

Please feel free to connect with me.