I have the following 4 tables, the sales value in which I need to comapre:
As seen, all columns are similar except for the sales one. Now I created an extra column in each of these tables by merging the similar columns and calling it KEY. Then, I created a new table with just the common columns and called it my master table. The KEY in the master table has a relationship many-to-one to the KEY columns in each of the other tables. In the master table, I've also created certain measures to perform some calculations.
My aim is to create a table/matrix visual with the above common columns and the subequent columns will be sales' data from Extract A01, Extract A02 ..... But each row shows the total and the measures don't seem to work.
I've tried it many ways. By creating and removing tables, by creating and deleting relationships, but nothing seems to work.
1 ACCEPTED SOLUTION
Hello @Anonymous
why dont you append the 4 tables into one consolidated table from power query.
you can add a new column into each extract, specifying the extract number,
and then append .
if this doesnt work for you, could you please share w visual presentation of the desired output ?
best regards,
Hi @Anonymous
You can try this method as follows.
Use Merge Queries:
Expand the table:
Use the above method to merge other tables.
Output:
Best Regards,
Yulia Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-xuxinyi-msft Thank you for the reply. I'll definitely try this method, but fo now, appending the tables has done it.
Hello @Anonymous
why dont you append the 4 tables into one consolidated table from power query.
you can add a new column into each extract, specifying the extract number,
and then append .
if this doesnt work for you, could you please share w visual presentation of the desired output ?
best regards,
Hello @Daniel29195
Thank you for the response. Here is my desired output.
Please note that the above and what I've shared before are mock data, hence the tables are so short. My datasets are huge with around 170 columns and thousands of rows. And they keep updating. All columns are similar and have similar names and identifier values, except for the columns that have sales or such data (though the columns names are still the same). For the desired output, there must be proper relationships between the tables so I can use slicers with identifier data. (for instance, slice per product, per plant, per cutomer etc.).
I just hope appending all the four datasets doesn't crash Power BI. I'll give a shot to what you're suggesting, but if you can think of another solution as well, that'd be highly appreciated. Thanks.
