Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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.

TopicSummarySEARCH_ASEARCH_my_valueSEARCH_textFOUND
AA quick brown...nullnullnullnull
BA quick brown...nullF1234ABCF1234, ABC
CA quick brown...AnullnullA
DA quick brown...nullF3465nullF3465

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

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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.

Eyelyn9_0-1652425340173.png

 

 

Otherwise, you may follow my workaround which may be  a little complex.

1. Add a Index column to the original table

Eyelyn9_1-1652425684433.png

2. Duplicate the original table to add a new table --> Select Index column, unpivot other columns

Eyelyn9_2-1652425856307.png

3. Filter all rows when Attribute column contains "SEARCH_" and Value <>"null" , then only remains Index and Value columns

Eyelyn9_3-1652426258637.png

4.Group Values by Index column and then renamed "FOUND":

Eyelyn9_5-1652426389733.png

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:

Eyelyn9_9-1652426836966.png

 

Final output:

Eyelyn9_8-1652426802614.png

 

 

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.

View solution in original post

6 REPLIES 6
v-eqin-msft
Community Support
Community Support

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.

Eyelyn9_0-1652425340173.png

 

 

Otherwise, you may follow my workaround which may be  a little complex.

1. Add a Index column to the original table

Eyelyn9_1-1652425684433.png

2. Duplicate the original table to add a new table --> Select Index column, unpivot other columns

Eyelyn9_2-1652425856307.png

3. Filter all rows when Attribute column contains "SEARCH_" and Value <>"null" , then only remains Index and Value columns

Eyelyn9_3-1652426258637.png

4.Group Values by Index column and then renamed "FOUND":

Eyelyn9_5-1652426389733.png

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:

Eyelyn9_9-1652426836966.png

 

Final output:

Eyelyn9_8-1652426802614.png

 

 

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.

CNENFRNL
Community Champion
Community Champion

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"

CNENFRNL_0-1652234087747.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

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

speedramps
Super User
Super User

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! 

Anonymous
Not applicable

Hi @speedramps@danextian,

 

sorry for not being very accurate when writing my question.

 

Input data would be something like this: 

TopicSummaryDateSEARCH_ASEARCH_my_valueSEARCH_text
AA quick brown...01.01.2022nullnullnull
BA quick brown...01.01.2022nullF1234ABC
CA quick brown...01.01.2022Anullnull
DA quick brown...01.01.2022nullF3465null

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):

TopicSummaryDateSEARCH_ASEARCH_my_valueSEARCH_textFOUND
AA quick brown...01.01.2022nullnullnullnull
BA quick brown...01.01.2022nullF1234ABCF1234, ABC
CA quick brown...01.01.2022AnullnullA
DA quick brown...01.01.2022nullF3465nullF3465

 

Thanks for your help, feel free to ask if anything is unclear.

Moritz

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.










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.