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

 

Please tell me solution.

 

REgards,

Ravi

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.
     

3 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

     

  • 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

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    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.