Creating of model table in PowerPivot

[NorthLegion]

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!

[az38]

@NorthLegion 

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])
)
)

[NorthLegion]

@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!

[jeremyking77]

@NorthLegion is it possible for you to attach the pbix file? or is it sensitive information?

[jeremyking77]

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