Forum Discussion
Column values into one single row value
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
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"
3 Replies
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
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!
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"
