Dynamically Create Column For Each Item In Order

[Anonymous]

Hello,

Looking to create a custom table based on another table.

I have a table called "order_id_product" which has order_id and the product information.

The two fields in question are:

order_id

product_title

I want to create another table, say "basket_analysis" which takes the order_id and then dynamically creates a column for each item in the order. I have seen DAX solutions which do 2 columns but I was expecting PQ to be better for this and also have better dynamic ability.

Expected outcome would look something like this:

order_id	product_1	product_2	product_3	product_4	product_5
12345	title_1	title_2
12346	title_1	title_2	title_3
12347	title_1
12348	title_1	title_2	title_3	title_4	title_5

Would anyone be able to assist on this as I have zero PQ knowledge.

Huge thank you in advance!

[johnt75]

Can I ask what you are trying to achieve by this? It would seem like this would only make any calculations you want to do in a report much more complicated as you don't know which column a product will appear in for any given order. Perhaps there are other approaches which can give your desired outcome.

[Anonymous]

Hi,

I do agree and being new to Pbi, I am trying to find ways to conduct analysis like basket analysis. I was thinking I could use the table for product basket analysis.

I'm still searching far and wide for the best method to do this and appreciate your reply showcasing concerns.

[johnt75 · Accepted solution]

Have a look at https://www.daxpatterns.com/basket-analysis/ . DAX Patterns is by the SQLBI guys, they're awesome.