Change dynamic list into column headers and add to a table with dates. Then unpivot the new table.

[Anonymous]

Hi there,

In short:
How can I transform a list into column headers and add these column headers to a table with dates?

As an example, we have 100 000 products across 10 catagories that are sold daily, however, not every product is sold every day. In fact, some products aren't sold at all. Now, I'd like to visualize in a chart how many % of the total available products in store are sold per day. When I zoom (drill?) into the chart, I'd like to see the "unsold products" for that day.

Problem:
The unsold products aren't mentioned in the sales anywhere. Using a measure I was able to create a chart, showing the sales per day (create table with dates + add column (total sales for that day / total number of products (=MEASURE)). However, the chart isn't interactive. When I use a slicer to select a different catagory, the sales percentages remain the same (because the data of the measure was placed into a column)

Attempt:
If I can somehow create a chart with all the dates and a list of all the products in store per day, then I just need to indicae which products are sold that day and which ones aren't. The result is an interactive chart and that is exactly what I need. But how can this table be created? I have a list of dates and a list of all the products in store. The list of products changes every month. I can generate a "Date Table", but then I need to add the products as column headers to this "Date Table". Once that is available I cun "unpivot" the table and I'll be ablo to indicate which products and how many have been sold / unsold each day.

Any suggestions on how to resolve this?

[v-yuta-msft]

@Anonymous ,

In general, this could be done in query editor, not in dax. You can try select the required columns, then try "Pivot Columns" and "Unpivot Columns". 

If you could share some sample data and give the expected result, I could do further analysis.

Community Support Team _ Jimmy Tao

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

[Anonymous]

Hi @v-yuta-msft,

Thank you for your response. Yes, Query Editor is probably the place to solve the riddle. Ok, here is some sample data:

List 1

1 Jan

2 Jan

3 Jan

List 2

Prod 1

Prod 2

Prod 3

Somehow combine the 2 lists into the table below:

Table A

Date     Prod 1     Prod 2     Prod 3

1 Jan     1             1              1 (1 = dummy data, as long as the value isn't NULL)

2 Jan     1             1              1

3 Jan     1             1              1

Once I have the table, I'll know how to "unpivot" this into the table below (desired result):

Table B

1 Jan   Prod 1

1 Jan   Prod 2

1 Jan   Prod 3

2 Jan   Prod 1

2 Jan   Prod 2

2 Jan   Prod 3

3 Jan   Prod 1

3 Jan   Prod 2

3 Jan   Prod 3

Note, the dates + type and number of products keep on changing every month.

Thank you,

[v-yuta-msft · Accepted solution]

@Anonymous ,

Click on the three columns [Prod 1], [Prod 2] and [Prod 3], then click "Unpivot Columns", you will achieve two new columns [Attribute] and [Value], remove the [Value] column you will get the result table as below:

 

Community Support Team _ Jimmy Tao

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

[Greg_Deckler]

Well, that sounds like a pivot in Power Query to start with. Sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi Greg,

Thank you for the link. Much appreciated.