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.
Hi everyone!
There is my case: I have few data tables in PowerPivot with similar types of data. In these tables I created couple calculated column and it work correct. For creating of pivot tables I try to unite these tables between calculated column in model table, where should be unique values. In PowerBI this is very ordinary task, I need just create new table with:
SomeName=
DISTINCT(UNION(
VALUES(Data_Table1[ColumnName]);
VALUES(Data_Table2[ColumnName]))
But in PowerPivot the same doesn't work! It returned mistake: "A single value was expected, but a table or multiple values was passed". So, I don't have any ideas, what I do wrong.
Thanks for any help!
I think the problem could be in VALUES()
This function cannot be used to Return values into a cell or column on a worksheet; rather, you use it as an intermediate function, nested in a formula, to get a list of distinct values that can be counted or used to filter or sum other values.
try to use SELECTCOLUMNS():
SomeName=
DISTINCT(UNION(
SELECTCOLUMNS(Data_Table1; "ColumnName"; Data_Table1[ColumnName]);
SELECTCOLUMNS(Data_Table2; "ColumnName"; Data_Table2[ColumnName])
)
)
@az38 ,
Unfortunately, SELECTCOLUMNS() doesn't work too. How I known from another BI-forum, its impossible to create calculated tables in PowerPivot, so single solution - clear data just in PowerQuery (although in this case it would be more convenient in PowerPivot).
@jeremyking77 , no, its not about uppercase/lowercase - I tried to do the same with just one table with small amount of data (about 10 lines), its doesn't work too.
Nonetheless, thanks for help!
@NorthLegion is it possible for you to attach the pbix file? or is it sensitive information?
It sounds like your column you're assuming is unique isnt actually unique.
Have you tried removing duplicates in Power Query?
Removing duplicates will make sure its unique, but its probably a good idea to make it upper case.. as 'A' and 'a' are not duplicates in Power Query, but it is with your relationships
There is a good article here on it
https://radacad.com/remove-duplicate-doesnt-work-in-power-query-for-power-bi-here-is-the-solution
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
18 | |
15 |