Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have one table
I created two filtered tables based on this table, called T1 and T2
I need to compare specific values between T1 and T2, so I want to merge them based on common productID.
Reading the documentation, I should be able to
- edit queries
- merge queries
Unfortunately, the two tables I made, T1 and T2, do not appear. The only table avaiable to manipulate is the original one
Am I missing something?
Solved! Go to Solution.
@ahuhn Before pivoting, please Change the DataType of "StatusLevel" to "WholeNumber" and give a try...
Proud to be a PBI Community Champion
@ahuhn I can understand that you have created two filtered tables T1 and T2 in "Data" pane using DAX. Then you are trying to merge these two tables in "Power Query" (but unfortunately you can't see them).
It is because, you can't see the calculated tables and fields that are created in "Data" pane (using DAX) in Power Query Editor.
You need to either do the filter tables T1 and T2 in Power Query itself and then Merge, or you need to merge in DAX itself.
Proud to be a PBI Community Champion
Thank you for the useful comments. To add to the confusion, I am new to powerBI.
I have (+80 extra columns):
product | statusLevel | date |
1 | 1 | 201809 |
2 | 1 | 201809 |
3 | 1 | 201809 |
1 | 1 | 201810 |
2 | 1 | 201810 |
3 | 2 | 201810 |
4 | 1 | 201810 |
I am subsetting on date for T1 and T2, to obtain two tables.
I want to merge T1 and T2 in product so that I can accurately compare the statusLevel change bewteen the two dates.
I cannot find a merge function in DAX, nor find a way to create a filtered table in the power query.
Any suggestions are welcome
T1:
product | statusLevel | date |
1 | 1 | 201809 |
2 | 1 | 201809 |
3 | 1 | 201809 |
T2:
product | statusLevel | date |
1 | 1 | 201810 |
2 | 1 | 201810 |
3 | 2 | 201810 |
4 | 1 | 201810 |
I want to merge to get:
product | statusLevel201809 | statusLevel201810 |
1 | 1 | 1 |
2 | 1 | 1 |
3 | 1 | 2 |
4 | 1 |
@ahuhn Please try "Pivot" option on the subset table (just with the required columns you are interested as mentioned),
Just try "Pivot" for Date field on the new table (trimmed version) in Power Query and the output will be as expected.. Not required to have two tables and then merge...
Proud to be a PBI Community Champion
Love that idea!
Unfortunately, I get the following error:
Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=Type
@ahuhn Before pivoting, please Change the DataType of "StatusLevel" to "WholeNumber" and give a try...
Proud to be a PBI Community Champion
Hi @ahuhn,
If you create summarized table or filtered table, you are right you can not find into edit queries that is default from power bi side.
Although if you want to merger those table for some visuals so there is alternative to do so.
As you mentioned both table have common id so just join that table in Relatonships tab on that id and use as you want.
Best Regards,
Ravi
Or just share some sample data or pbix file. which two column you want to compare.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |