Column values into one single row value

[Anonymous]

I have a column that looks as follows:

Cost Center

123,

234,

245,

543,

345,

I would like another column from the above to look as follows:

Cost Center

123,

234,

245,

543,

345, 

New Column

123, 234, 245, 543, 345 

123, 234, 245, 543, 345 

123, 234, 245, 543, 345 

123, 234, 245, 543, 345 

123, 234, 245, 543, 345 

[AlB]

Hi @Anonymous 

Try this:

NewCol = CONCATENATEX(Table1, Table1[Cost Center], ", ") 

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[Anonymous]

@AlB Sorry, I forgot to mention that I need it in the edit query mode. My problem is as follows:

1. I need to achieve the output in the edit query mode.

2. I have a seperate table called SETUP :

Cost Center             ID

*                              467773

*                              893774

I would like to replace each * with those concatenated values in the edit query mode. So that each time the first table is refreshed, the copied values in SETUP table also changes accordingly. So my set up table should look like:

Cost Center                                ID

123,234,245,543,345                  467773

123,234,245,543,345                  893774

Could you please help me with achieving the concatenation in edit query, followed by how to replace * with those concatenated values in the SETUP Table (Another Table)?  Thank you!

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Please check the following steps as below.

1. Transpose the fact table and add a custom column in it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQy1lGK1YlWMjI2gTJMTCEMUxOolDFYJBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [costcenter = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each [Column1] & "" &[Column2]& "" &[Column3]& "" &[Column4]& "" &[Column5]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1", "Column2", "Column3", "Column4", "Column5"})
in
    #"Removed Columns"

2. After that, add the column as a new query.

3. Then we can add a custom column in cost center table like this and extract the values.

=if [Cost Center] = "*" then Custom else 0

M code for your reference as well.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0lLSUTIxMzc3N1aK1YFwLSyNzc1NlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Cost Center" = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cost Center", type text}, {"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Cost Center] = "*" then Custom else 0),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values"