Forum Discussion
Concatenate specific columns with prefix without null
I got a table where values are stored in columns with the Prefix "SEARCH_" (number and name of the columns may change) and want to add a concatenated column "FOUND". The null values should be ignored.
| Topic | Summary | SEARCH_A | SEARCH_my_value | SEARCH_text | FOUND |
| A | A quick brown... | null | null | null | null |
| B | A quick brown... | null | F1234 | ABC | F1234, ABC |
| C | A quick brown... | A | null | null | A |
| D | A quick brown... | null | F3465 | null | F3465 |
What is the right Power Query M Code to solve this problem?
I already thought about someting like this, but had problems to figure out how to define the type of the columns dynamic
"Added Custom" = Table.AddColumn(#"Previous Step", "FOUND", each Text.Combine(
List.Select( Record.FieldValues( Record.FromList(
List.Select(Table.ColumnNames(fileBinary as table) as list, each Text.Contains(_, "SEARCH_")) as list, type text)), each _<> "" and _ <> null)
,", "))
Thanks
Moritz
Hi Anonymous ,
If you could count how many the columns that without "SEARCH_" prefix and make sure they are sorted before columns that with "SEARCH_" prefix, I'd suggest you use CNENFRNL 's method by just change the number in List.Skip function, as shown below. I think it's the most efficient.
Otherwise, you may follow my workaround which may be a little complex.
1. Add a Index column to the original table
2. Duplicate the original table to add a new table --> Select Index column, unpivot other columns
3. Filter all rows when Attribute column contains "SEARCH_" and Value <>"null" , then only remains Index and Value columns
4.Group Values by Index column and then renamed "FOUND":
Here is the whole M syntax of the New Table:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJUKCzNTM5WSCrKL8/T09MDChkY6gGRkYGREZCTV5qTg0bF6kQrORGv1c3QyNgEpNzJGazVmQitjlisdCHBSmMTM1O43lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Topic = _t, Summary = _t, Date = _t, SEARCH_A = _t, SEARCH_my_value = _t, SEARCH_text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Topic", type text}, {"Summary", type text}, {"Date", type date}, {"SEARCH_A", type text}, {"SEARCH_my_value", type text}, {"SEARCH_text", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"), #"Added Custom1" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Contains([Attribute],"SEARCH") and [Value]<>"null"), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = true)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom","Attribute"}), #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Index"}, {{"Count", each Text.Combine([Value] , ","), type text}}), #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Count", "FOUND"}}) in #"Renamed Columns"5. Back to the original table, Merge Queries --> Expand columns:
Final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJUKCzNTM5WSCrKL8/T09MDCkFRrE60khNOFW6GRsYmIFknZ7BKZ+wqHZEMc8FtmLGJmSlYWSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Topic = _t, Summary = _t, SEARCH_A = _t, SEARCH_my_value = _t, SEARCH_text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Topic", type text}, {"Summary", type text}, {"SEARCH_A", type text}, {"SEARCH_my_value", type text}, {"SEARCH_text", type text}}), #"Combined Text" = Table.AddColumn(#"Changed Type", "FOUND", each Text.Combine(List.Select(List.Skip(Record.ToList(_),2), each Text.Length(_)>0), ", ")) in #"Combined Text"Hi CNENFRNL,
This couldn't solve my problem, because there may be more than two descriptive columns. For more information see my reply to speedramps.
Thanks
Moritz
I dont undestand what you want, but I would like to help 😀
Please provide an example of your input data
and an example of your desired output data
with a very clear desciption of what you need.
Please provide them in table format that we can import to build a solution. Dont send screen shots.
Hide any private data.
Thank you!
Hi speedramps
I couldn't agree more.
hi Anonymous ,
Could you please elaborate your problem? Like which columns have to be concatenated and based on what conditions.Hi speedramps, danextian,
sorry for not being very accurate when writing my question.
Input data would be something like this:
Topic Summary Date SEARCH_A SEARCH_my_value SEARCH_text A A quick brown... 01.01.2022 null null null B A quick brown... 01.01.2022 null F1234 ABC C A quick brown... 01.01.2022 A null null D A quick brown... 01.01.2022 null F3465 null Please note: The number, column name and type of descriptive columns (without "SEARCH_" prefix) can vary and the number and name of search columns (with "SEARCH_" prefix) can also vary, but they are always type text.
The desired output data would be a new column "FOUND", which should contain the concatenated, comma speparated list of all available search columns (null should be ignored):
Topic Summary Date SEARCH_A SEARCH_my_value SEARCH_text FOUND A A quick brown... 01.01.2022 null null null null B A quick brown... 01.01.2022 null F1234 ABC F1234, ABC C A quick brown... 01.01.2022 A null null A D A quick brown... 01.01.2022 null F3465 null F3465 Thanks for your help, feel free to ask if anything is unclear.
Moritz
Hi Anonymous ,
If you could count how many the columns that without "SEARCH_" prefix and make sure they are sorted before columns that with "SEARCH_" prefix, I'd suggest you use CNENFRNL 's method by just change the number in List.Skip function, as shown below. I think it's the most efficient.
Otherwise, you may follow my workaround which may be a little complex.
1. Add a Index column to the original table
2. Duplicate the original table to add a new table --> Select Index column, unpivot other columns
3. Filter all rows when Attribute column contains "SEARCH_" and Value <>"null" , then only remains Index and Value columns
4.Group Values by Index column and then renamed "FOUND":
Here is the whole M syntax of the New Table:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJUKCzNTM5WSCrKL8/T09MDChkY6gGRkYGREZCTV5qTg0bF6kQrORGv1c3QyNgEpNzJGazVmQitjlisdCHBSmMTM1O43lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Topic = _t, Summary = _t, Date = _t, SEARCH_A = _t, SEARCH_my_value = _t, SEARCH_text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Topic", type text}, {"Summary", type text}, {"Date", type date}, {"SEARCH_A", type text}, {"SEARCH_my_value", type text}, {"SEARCH_text", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"), #"Added Custom1" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Contains([Attribute],"SEARCH") and [Value]<>"null"), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = true)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom","Attribute"}), #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Index"}, {{"Count", each Text.Combine([Value] , ","), type text}}), #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Count", "FOUND"}}) in #"Renamed Columns"5. Back to the original table, Merge Queries --> Expand columns:
Final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
