Forum Discussion

lravikiran's avatar
lravikiran
Icon for Helper I rankHelper I
6 years ago
Solved

Concatenate 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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    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
    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's avatar
Anonymous
Not applicable
6 years ago

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