Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Change cell Values by combining upper and lower cell value

 

Hi,

 

As you can see in the snapshot, I want to change cell values by concating the upper and lower values. Like CURRENT_BUDGET,CURRENT_VARIANCE, CURRENT_VAR%. Then, PY CUR MON_ACTUAL and so on. Then convering these to column names. Also how if I want to make separate tables for CURRENT, PY CUR MON etc?

 

Thanks,

 

Amit Kumar

Need Help
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Anonymous ,

     

    Using below M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg4NCnL1C1HSUYKggEgFoJCCr7+fUqxOtJJTqIu7K0g2zDHI09HP2RXCVAVSjs4hoY4+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column4","Column5"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Added Custom" = Table.AddColumn(#"Filled Down", "Custom", each [Column1]&"_"&[Column2]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Custom", "Column1", "Column2"}),
    #"Transposed Table1" = Table.Transpose(#"Reordered Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"CURRENT_BUDGET", type text}, {"CURRENT_VARIANCE", type text}, {"CURRENT_VAR%", type text}, {"PY CUR MON_ACTUAL", type text}})
in
    #"Changed Type1"

    And you will see:

    For the related .pbix file,pls see attached.

     

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

4 Replies

  • Jakinta's avatar
    Jakinta
    Solution Sage
    5 years ago

    For 1st part of your question you can apply some of steps below...

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQIiJ6VYnWglQyDLCIiNwbwKIAuCSeDFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down1" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Transposed Table1" = Table.Transpose(#"Filled Down1"),
    Custom1 = let t=Table.ToRows(#"Transposed Table1") in Table.FromRows(List.RemoveFirstN(t,2), List.Transform(List.Zip({t{0},t{1}}), each Text.Combine(_,"_")))
in
    Custom1

    or with UI only

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQIiJ6VYnWglQyDLCIiNwbwKIAuCSeDFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down1" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down1",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"

     

    ...after that it is pretty straightforward to create desired tables...

    P.S. It is important to have null value in order for FillDown to work.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Are you able to share the source file so that I can undersatand more better. I am copying and pasting the script in the blank query but it is not working.

       

      Thanks

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi Anonymous ,

         

        Using below M code:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg4NCnL1C1HSUYKggEgFoJCCr7+fUqxOtJJTqIu7K0g2zDHI09HP2RXCVAVSjs4hoY4+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column4","Column5"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Added Custom" = Table.AddColumn(#"Filled Down", "Custom", each [Column1]&"_"&[Column2]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Custom", "Column1", "Column2"}),
    #"Transposed Table1" = Table.Transpose(#"Reordered Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"CURRENT_BUDGET", type text}, {"CURRENT_VARIANCE", type text}, {"CURRENT_VAR%", type text}, {"PY CUR MON_ACTUAL", type text}})
in
    #"Changed Type1"

        And you will see:

        For the related .pbix file,pls see attached.

         

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!