Assinning all value to each value of other table

[alikhalilov]

Hi, 

I have two excel file.  One of them is Market details and other is Product details. 

If I want to assign all products to each markets and apply it on new table model how can i do it by Power Query? 

note: it is important that, When I assing a new product or create new market this model should be able to refresh and assign all product to each markets. 


in picture suppose all  data tables  are in different excel file. It is just for having overview info for you.

Thanks for your kind feed-backs

[Jimmy801 · Accepted solution]

Hello @alikhalilov 

in DAX you can use Crossjoin to do this. In Power Query you can add a new column to one of the table, referencing your second table. Then expand the table-column. Here a simple example

let
    Tbl1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgeJYnWglIyALgmNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    Tbl2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjZU0lFyMVSK1QGyjUBsI6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C = _t, D = _t]),
    #"Added Custom" = Table.AddColumn(Tbl2, "Custom", each Tbl1),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"A", "B", "C"}, {"A", "B", "C.1"})
in
    #"Expanded Custom"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[v-alq-msft · Accepted solution]

Hi, @alikhalilov 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table 1:

Table 2:

You may create a new query with the following m codes in 'Advanced Editor'.

let
    Source = Table.AddColumn(Table.Distinct( #"Table 1"),"Tab",each #"Table 2"),
    #"Expanded Tab" = Table.ExpandTableColumn(Source, "Tab", {"Product", "Product code"}, {"Product", "Product code"})
in
    #"Expanded Tab"

Result:

Or you may create a calculated table with the following dax.

Table = 
CROSSJOIN(
    DISTINCT('Table 1'),
    'Table 2'
)

Result:

Best Regards

Allan

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

[Jimmy801 · Accepted solution]

Hello @alikhalilov 

in DAX you can use Crossjoin to do this. In Power Query you can add a new column to one of the table, referencing your second table. Then expand the table-column. Here a simple example

let
    Tbl1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgeJYnWglIyALgmNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    Tbl2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjZU0lFyMVSK1QGyjUBsI6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C = _t, D = _t]),
    #"Added Custom" = Table.AddColumn(Tbl2, "Custom", each Tbl1),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"A", "B", "C"}, {"A", "B", "C.1"})
in
    #"Expanded Custom"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Payeras_BI]

Hi @alikhalilov ,

It seems you want to CrossJoin both tables in PQ.

See if this helps you:

How to Crossjoin in Power Query 

Regards,