Change cell Values by combining upper and lower cell value

[Anonymous]

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

[Jakinta]

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]

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 · Accepted solution]

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!

[Jakinta]

Source file is already in there. 1st step. Source.

Create blank query and paste the code in Advanced editor.

Just tested it now, it is working.

Paste = replace any existing code with the new one.