Share
ReportHi 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!
All responses (
Answers (
8,624
