Merge queries to get everything in one table

[sherishamarri]

Hello All,

I'm new to pwerbi and I have requirememt where we need to get data forn 3 different tables in to one table.

I have tried with merge queries and DAX formulas but the result is not that i expected, It is returning rows from all tables getting duplicate values.

And also there is a condition that for expense category F&B should be divided in to all attendees and speaker who ever attended the program EX:( for program 1 if attendee count is 3 and F&B is 300$ it should divide the amount to 100$ for each attendee and speaker but for category ffs only speaker data should reurn as ffs will only applies to speaker)

Sometimes for cancelled program no attendees will be there so I'm adding another table "Speaker" to get the amount of speakers ffs.

I have atteched the Expected Result Table for your reference.

I'm missing something easy may be but struck here form last 4 days.

Any help highly appreciated.

 

[v-jingzhan-msft · Accepted solution]

Hi @sherishamarri 

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!

[sherishamarri]

Thank you so much, this helped alot!!!!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...