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.
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.
Solved! Go to Solution.
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!
Are you using Power Query? Any chance can upload some sample data?
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!
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |