Power Platform Community Forum Thread Details

Hi friends!

I am a newbie at Power Automate, and I am working to automate a long work process, taking data from Power BI and then inserting it into another data management system. There is a many to one relationship between the two tables, leading to many rows with the same identifier.

The goal of this DAX query is to:

- Join the two tables in a left join

- Get all the columns needed from both tables

- Aggregate the measure fields, grouped by the identifier field

- Return the new table with one row per identifier field

I have tried all sorts of combinations of SUMMARIZE, SUMMARIZECOLUMNS, GROUPBY, etc. The issue I am finding is that if I gather all the data BEFORE doing the join, I can either do the left join, OR get the aggregation, but because the relationship between the two tables isn't naturally recognized, I have to do Table[identifier]&"" when selecting the identifier row in both tables, invalidating it in the GROUPBY and throwing the error that there are too many unique identifiers per row.

Now, I have been trying to do the join FIRST, and then get all the data with a GROUPBY. The issue I am running into is that the NATURALLEFTOUTERJOIN table is resulting in headers like this: Table[field]. Then if I try to reference that header, I have to do it like this NewTable[Table[field]] which throws an error because brackets aren't allowed in the field names.

EVALUATE

VAR JoinedTable = NATURALLEFTOUTERJOIN('Table1', 'Table2')

VAR GroupedTable =

GROUPBY(JoinedTable,

JoinedTable[Table1[item1]],

JoinedTable[Table1[item2]],

Etc.

"itemx", SUMX(CURRENTGROUP(), JoinedTable[Table2[itemx]]))

RETURN GroupedTable

Does anyone know of any way to go around this? I am super familiar with SQL, but it is messing up the way I think about table queries. Any help is super appreciated!

Categories:

General topics