Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello dear PowerBI community,
I want to create a table but with multiple 1:n relationships in it.
I don´t know if or how it´s possible to create such a table, so I hope for your help.
To make it easier to explain, I created a sample in Excel of what I would need:
It´s an list of sales documents with it´s articles and payment types related to it.
Here is a short explanation of the table columns and rows:
A - C: Sales document data (Header information)
D - F: Article data that are related 1:n to the sales document data
G - H: Payment data that are related 1:n to the sales document date
The data source is already available in this schema.
1. The different entities. It´s not needed in the visual. It´s only for explanation.
2. Table header
3. - 5. First sales document with 3 articles and 2 payment types
6. - 8. Second sales document with 1 article and 3 payment types
9. Third sales document with 1 article and 1 payment type
Is it possible to create a table like this in PowerBI?
It would be importan to have all the information in one table because the user shouldn´t need to search sales document data about different tables.
Best regards
Oliver
Hello,
if you need to create such table from 3 tables you can do that in power Query using merge Query it is the same concept of joining tables in SQL. the only thing that you need to check if that the 3 tables contains a column that is common between them in order to merge them
Did i answer your Question? Do not forget to mark it as solution!
Hello,
thanks for your answer.
Yes I have a column "DocumentID" which is present in each of these tables and I know the merge function in PowerQuery. But unfortunately I don´t know how to create a table as in the screenshot above.
If I merge these tables into one table, I have several redundant information as in this scrrenshot:
But the goal is to display a table in the report with this schema:
Best regards
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |