Forum Discussion
Merge queries to get everything in one table
You can follow below steps to get what you want.
1. Rename columns in "Speaker" table to make them the same as those in "Attendee".
2. Append "Speaker" table to "Attendee". Append queries - Power Query
3. Merge "Attendee" to "Expense" by "Pgm Name" column with left outer.
4. Add three custom columns one by one:
Modified Attendee:
if [Category] = "FFS" then Table.SelectRows([Attendee], each [Type] = "Speaker") else [Attendee]Count:
Table.RowCount([Modified Attendee])New Amount:
[Amount]/[Count]5. Expand the first custom column and only select the columns you need to expand.
6. Remove unnecessay columns and rename columns.
The final expense table:
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
You can follow below steps to get what you want.
1. Rename columns in "Speaker" table to make them the same as those in "Attendee".
2. Append "Speaker" table to "Attendee". Append queries - Power Query
3. Merge "Attendee" to "Expense" by "Pgm Name" column with left outer.
4. Add three custom columns one by one:
Modified Attendee:
if [Category] = "FFS" then Table.SelectRows([Attendee], each [Type] = "Speaker") else [Attendee]Count:
Table.RowCount([Modified Attendee])New Amount:
[Amount]/[Count]5. Expand the first custom column and only select the columns you need to expand.
6. Remove unnecessay columns and rename columns.
The final expense table:
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Thank you so much, this helped alot!!!!