Home Return multiple columns from separate query when condition matches power query

Questions

Return multiple columns from separate query when condition matches power query

October 5, 2022

I am attempting to achieve the following:

I wish to match the SectionNames with the data in Source using Text.Contains as my condition.

Currently, I am only able to achieve this for a single column however, I have added a Filter Column to this, which I also wish to pull through, but I am unsure how to achieve this.

M Code to generate current:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}}),
    Custom1 = Table.AddColumn(#"Changed Type", "SectionName",  (x) => Text.Combine(Table.SelectRows(Table25, each Text.Contains(x[Source],[SectionName], Comparer.OrdinalIgnoreCase))[SectionName],", "))
in
    Custom1

Data:

Table1:

SECTION 1:  abc
text
SECTION 2: dcb
text
SECTION 3: zzz

List to Match by condition:

SectionName Filter
SECTION 1:  null
SECTION 2:  X
SECTION 3:  null

Additionally, I notice that the above code returns blank instead of null. I guess this is because combining two nulls gives a blank.

>Solution :

Try this:

let
   Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}}),
   Custom1 = Table.AddColumn(#"Changed Type", "SectionName",  (x) => Table.SelectRows(Table25, each Text.Contains(x[Source],[SectionName], Comparer.OrdinalIgnoreCase))),
    #"Expanded SectionName" = Table.ExpandTableColumn(Custom1, "SectionName", {"SectionName", "Filter"}, {"SectionName.SectionName", "SectionName.Filter"})
in
    #"Expanded SectionName"