Forum Discussion
Combining data into one bar chart
Sure thing.
The basic idea is that we need to unpivot both tables and then build a relationship between them.
To unpivot the tables go into Power Query via the Transform data icon
On Table1 select the Product column and then from the Transform portion of the ribbon select Unpivot Other Columns.
I renamed the resulting "Attribute" column as "Category"
Do the same for Table2 selecting the "Code" column and unpivoting the other columns
In this case I rename the "Attribute" column to "Product" to match the column name in Table1
Close and Apply these changes to go back into the BI screen.
You can now set a Many to Many relationship between the two tables using the product columns. It will filter in both directions.
The measure I used was;
Both Y =CALCULATE(COUNT(Table1[Category]),FILTER(Table2,Table2[Value]="Y"),FILTER(Table1,Table1[Value]="Y"))Hope this helps.
If I understand correctly, your data looks something like;
with the second table continuing to include up to SKU 25.
And your desired result is something like
Correct?
Hi,
Yeah that's the kind of thing I'm after. Are you able to explain how you did that please?
The source data is in a Microsoft access database if that helps.
