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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
sashaxiv
Frequent Visitor

DAX/PowerQuery Check if value is contained in 1-N list of values in another table

Good morning all!

I would like to know the best way to deal with this problem.

 

I need to check for each value (ID_TASK_ORDER) in table A (ID-ID) , if it is contained in any posible combination in table B (ID- {List of ID's})

 

I was thinking of firstly Summarizing tabla B and then relate this new table and table A.

Do you think this is the way to solve the problem? 

How could I create with DAX the summary table with all distinct values?

 

sashaxiv_3-1712906056120.png

Thanks in advance!

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @sashaxiv ,

 

Thanks @lbendlin  for the quick reply. I have some other thoughts to add:

(1) In power query, copy a table B. Click "Advanced Editor" to copy and paste the following code.

vtangjiemsft_0-1713162050413.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUap21HGqVYrVgfOcITwjIM8VzDIGstyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_TASK = _t, ORDERS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID_TASK", Int64.Type}, {"ORDERS", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ORDERS", Splitter.SplitTextByDelimiter("{", QuoteStyle.Csv), {"ORDERS.1", "ORDERS.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ORDERS.1", type text}, {"ORDERS.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "ORDERS.2", Splitter.SplitTextByDelimiter("}", QuoteStyle.Csv), {"ORDERS.2.1", "ORDERS.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"ORDERS.2.1", type text}, {"ORDERS.2.2", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type2",{"ORDERS.1", "ORDERS.2.1", "ORDERS.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "ORDERS"}}),
    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"ORDERS", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ORDERS"),
    #"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter2", {"ORDERS"})
in
    #"Removed Duplicates"

Then the result is as follows.

vtangjiemsft_1-1713162111127.png

We can create a table in desktop.

Table C = SUMMARIZE('Table B (2)','Table B (2)'[ID_TASK],"ORDERS",CONCATENATEX ( 'Table B (2)' , [ORDERS] , ","))

vtangjiemsft_2-1713162167146.png

We can create a column in Table A.

IS_A_IN_B = 
var _table= SELECTCOLUMNS(ADDCOLUMNS('Table B (2)',"task_order",[ID_TASK] &"-" & [ORDERS]),"i_t_o",[task_order])
RETURN IF([ID_TASK_ORDER] in _table,1,0)

vtangjiemsft_3-1713162230689.png

Best Regards,

Neeko Tang

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

4 REPLIES 4
Anonymous
Not applicable

Hi @sashaxiv ,

 

Thanks @lbendlin  for the quick reply. I have some other thoughts to add:

(1) In power query, copy a table B. Click "Advanced Editor" to copy and paste the following code.

vtangjiemsft_0-1713162050413.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUap21HGqVYrVgfOcITwjIM8VzDIGstyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_TASK = _t, ORDERS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID_TASK", Int64.Type}, {"ORDERS", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ORDERS", Splitter.SplitTextByDelimiter("{", QuoteStyle.Csv), {"ORDERS.1", "ORDERS.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ORDERS.1", type text}, {"ORDERS.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "ORDERS.2", Splitter.SplitTextByDelimiter("}", QuoteStyle.Csv), {"ORDERS.2.1", "ORDERS.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"ORDERS.2.1", type text}, {"ORDERS.2.2", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type2",{"ORDERS.1", "ORDERS.2.1", "ORDERS.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "ORDERS"}}),
    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"ORDERS", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ORDERS"),
    #"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter2", {"ORDERS"})
in
    #"Removed Duplicates"

Then the result is as follows.

vtangjiemsft_1-1713162111127.png

We can create a table in desktop.

Table C = SUMMARIZE('Table B (2)','Table B (2)'[ID_TASK],"ORDERS",CONCATENATEX ( 'Table B (2)' , [ORDERS] , ","))

vtangjiemsft_2-1713162167146.png

We can create a column in Table A.

IS_A_IN_B = 
var _table= SELECTCOLUMNS(ADDCOLUMNS('Table B (2)',"task_order",[ID_TASK] &"-" & [ORDERS]),"i_t_o",[task_order])
RETURN IF([ID_TASK_ORDER] in _table,1,0)

vtangjiemsft_3-1713162230689.png

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

 

 

 

 

sashaxiv
Frequent Visitor

Yes, but I could have a problem.

Column Order in table B may have 1 to N different values (the table will be connected to an Excel file). If I unpivot table B with powerquery, let's imagine that the first time N=4 (orders A,B,C,D), but a couple of days the file contains 5 orders in a file N=5 (orders A...E).

 

Once the table is updated I will be missing order E

 

 

Unpivot it as part of your Power Query script, so it always operates on the latest data.

lbendlin
Super User
Super User

Unpivot table B to bring it into a usable format. Then you can join directly via your key.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.