Create new query based on parameters

[EladAppelNN]

Hi, Lets say I have a table/query for products, and a table/query for countries

query1         |                  query2

Product        |                 Countries

-Tomato       |                  - US

-Onion         |                 - UK

-Pepper        |                 - Germany

I want to create a new table/query that will be a database;

query3 - NEW

Product    Countries

Tomato     US

Tomato     UK

Tomato     Germany

Onion       US

Onion       UK

Onion       Germany

Pepper      US

Pepper      UK

Pepper      Germany

is there a dax formula to create query3?

[sendilc]

Hi, 

Pls create 2 tables Products and Countries and then use the below PQ

let
Source = Product,
#"Added Custom" = Table.AddColumn(Source, "Custom", each Countries),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Countries"}, {"Countries"})
in
#"Expanded Custom"

Regards

[EladAppelNN]

Thanks for the detailed explanation, where should this be enterred? asa  new custom column?

[m_dekorte]

Hi @EladAppelNN,

Sure you could create that table with DAX, see here. 

However if you're bringing your data in and loading that into Power Query, it makes more sense to create it there. To process is really simple. Go to your Products (query1), select Add Custom column

In the dialog box enter the Countries (query2) name, press OK

You'll see a nested table in the new column and with those sideward arrows in the header you can extract fields.

Ps. If this helps solve your query please mark this post as Solution, thanks!