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.
I have a source table that I need to "clean up". The clean-up operation must be done entirely in Power BI. In particular, I need to correct some values in a column. The correct values are stored in another source table. For simplicity, the table to clean up is like:
Table A
ID SomeCol
1 a
2 b
3 d
4 e
5 g
The corrected data source is like:
Table B
ID AnotherCol
3 c
4 d
5 e
The desired transformation of table A would look like:
ID SomeCol
1 a
2 b
3 c
4 d
5 e
I am far more experienced in Qlik Sense than I am with Power BI. So, I'm having difficulty "un-learning" Qlik Sense to "think" in terms of Power BI. In Qlik Sense, to clean up table A, I would do something like:
-load table A
-load table B as a left join (on ID) to table A, preserving SomeCol and AnotherCol ("enhance" table A with data from table B)
-create derived (calculated) table C, which is simply a copy of table A, but with a "clean-up" statement for column SomeCol, e.g.,
If(
IsNull(AnotherCol) = -1 //no "corrected" data exists for the given row, i.e., it's already correct
,SomeCol
,AnotherCol
) as SomeCol
-drop table A (use table C moving forward)
I don't know if the "Qlik" solution method would work in Power BI. How can I achieve the desired result in Power BI?
Solved! Go to Solution.
Hi @Anonymous,
You can apply the same solution in the Power BI Desktop.
1. Open the Query Editor;
2. Merge the two queries as one;
3. Add a custom column;
4. Delete the unwanted columns.
You can find a demo in the attachment.
Best Regards,
Dale
Hi @Anonymous,
Could you please mark the proper answer as solution?
Best Regards,
Dale
Hi @Anonymous,
You can apply the same solution in the Power BI Desktop.
1. Open the Query Editor;
2. Merge the two queries as one;
3. Add a custom column;
4. Delete the unwanted columns.
You can find a demo in the attachment.
Best Regards,
Dale
Hi Dale!
I have the same problem, but with a big BUT. Second table with correct values is DirectQuery, so I can't just manually merge them and kill some bad values. I have a table with data of a stores visitation for 8 stores< but 2 of them are inaccurate and should be replaced with data from calculated table (sourced from DirectQuery). So I think it can be somehow solved with Power Query M functions, but have no idea how.
Both tables are 3 columns: Date, Store Number, Visitation.
I'm almost lost any hope of solving it.
Best regards, Taras
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |