Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Table 1:
Year Month ID PID
2017 02 90000 10000
2017 02 90000 10001
2017 02 90000 10002
2017 02 90000 10003
2017 02 90001 10004
Table 2:
Year Month ID Amt
2017 02 90000 10
2017 02 90001 20
2017 02 90002 30
2017 02 90003 40
Both these tables are pulled using power query and I am doing a left join between Table 1 (Left) and Table 2 (Right) within power query itself. I created a key in both the tables which concatenates Year, Month, and ID since in power query as multiple join statements are not allowed using power query GUI and I am joining on the same key.
As a result of this join, the values I am getting is:
Key ID PID Amt
2017029000 90000 10000 10
2017029000 90000 10001 10
2017029000 90000 10002 10
2017029000 90000 10003 10
And so on.
What I need to do is pivot this in Excel and just show '10' in Values field instead of (10+10+10+10) for the IDs and the PIDs.
Desired Output (In Pivot):
ID PID Amt
90000 10000 10
10001
10002
10003
I need a left join since there are many unmatched IDs in Table 1 which are required. Also, there are similar columns to PID in the table which are required attributes.
I had earlier tried to create a relationship between both these tables in pivot but since the Table 1 consists of duplicate IDs, it wouldn't let me do the same.
Can this be done in any way using DAX or in the pivot or even from the perspective of the join?
Solved! Go to Solution.
First, you should know that Power BI is different than Excel. There's no such excel pivot table. If you want to display your data with the format like excel pivot table. I would suggest you to choose Matrix.
Then based on your description, if you don't filter on PID, you want to show only one value i.e 10. And when you filter on PID, you want to show 10 in any filter context. Right?
However this is a contradiction in Power BI. When we want to achieve the first condition, we have to make other 10s be blank. Then if we want to achieve your second condition, we need all the 10s are not blank. Thus, I'm afraid your requirement cannot be achieved.
Thanks,
Xi Jin.
To achieve your requirement, first we need to add a Index column for your table. Them we can create a calculated column to compare the current row with previous row with DAX expression. If they are same, then show blank.
Expression is like this:
New Amt = IF ( Table1[Table2.Amt] = LOOKUPVALUE ( Table1[Table2.Amt], Table1[Index], Table1[Index] - 1 ), BLANK (), Table1[Table2.Amt] )
Same to ID column.
Thanks,
Xi Jin.
Hi Xi Jin,
Thank you for your reply. I have a slight change in my requirement.
Since all the PID's (10000, 10001, 10002, 10003) are associated with just one ID (90000), they should show just one value i.e 10(which looks up from table 2). In the current way the Amt gets associated with the first PID and in pivotting this, if I were to filter on the other PID's I would lose the Amt. And the resultant format desired is of a pivot table.
I have been trying to create a measure which would show 10 in any filter context which has any of those numbers but to no avail.
Any ideas?
Thanks Again!
First, you should know that Power BI is different than Excel. There's no such excel pivot table. If you want to display your data with the format like excel pivot table. I would suggest you to choose Matrix.
Then based on your description, if you don't filter on PID, you want to show only one value i.e 10. And when you filter on PID, you want to show 10 in any filter context. Right?
However this is a contradiction in Power BI. When we want to achieve the first condition, we have to make other 10s be blank. Then if we want to achieve your second condition, we need all the 10s are not blank. Thus, I'm afraid your requirement cannot be achieved.
Thanks,
Xi Jin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |