Forum Discussion
Left join merge without aggregating or expanding?
New to Power BI and stuck on a basic question. I'm trying to duplicate a simple report I currently generate using Python/Pandas.
I have a data table with 102 rows and a table with 160 rows to lookup a partner name. I can do a left outer merge which correctly matches all 102 rows in the data table. However in the resulting column I am only given the options to Expand or Aggregate the matching records, Expanding results in 160 rows (defeating the purpose of the left join and duplicating data) and Aggregating is not relevant since it is not a numeric field.
Thanks for any suggestions.
I figured out is was due to duplicate records in the lookup table. When I used the "Remove Rows - Duplicates" function on the lookup table the merge returns the correct result.
Interesting that my equivalent PANDAS function did not expand the resulting table, even with the duplicates:
orders2 = pd.merge(orders, partners, how='left', on='CustomerId')
PANDAS only expands the table if there are different lookup values for the same 'CustomerId' and ignores duplicates.
Interesting!
2 Replies
I figured out is was due to duplicate records in the lookup table. When I used the "Remove Rows - Duplicates" function on the lookup table the merge returns the correct result.
Interesting that my equivalent PANDAS function did not expand the resulting table, even with the duplicates:
orders2 = pd.merge(orders, partners, how='left', on='CustomerId')
PANDAS only expands the table if there are different lookup values for the same 'CustomerId' and ignores duplicates.
Interesting!