How to merge rows in a report

[monojchakrab]

Hi PBI community,

I have a report (screen shot attached) :city wise sales

Is it possible to merge the rows in this report so that cities which are on different rows - like Mumbai and Nvi Mumbai, or Kolkata and Bidhannagar, could actually be merged and seen together?

They are technically the same city (only PIN codes could be different).

Any help appreciated

Thanks and best regds.,

[TomMartens · Accepted solution]

Hey @monojchakrab ,

I have to admit that I do not fully understand your requirement.

Maybe you can create another column in your dimension table that contains the name, this column can be filled using a lookup table inside Power Query. This table contains two columns one that contains the original column value and the 2nd one contains the "lookup" value. You can join both tables using the Merge function of Power Query, the next to articles explain this feature in more detail:

• Merge queries overview - Power Query | Microsoft Docs
• Table.Join - PowerQuery M | Microsoft Docs

Depending on the nature of the join you can keep or remove the rows where the city is not contained in the lookup table, my recommendation is "keep". 

You can create a report(page) showing the cities that are not contained in the lookup table.

Here you will find a very little pbix file
https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/ER5HTPFrq8lOopj2IdRuQx0BAftaY...
that demonstrates what I described above. Next to "keeping"  the rows if a city is not contained in the lookup table (Left outer join), the resulting null value will be replaced.

Hopefully, this provides an idea of how to tackle this challenge.

Regards,

Tom

[TomMartens · Accepted solution]

Hey @monojchakrab ,

I have to admit that I do not fully understand your requirement.

Maybe you can create another column in your dimension table that contains the name, this column can be filled using a lookup table inside Power Query. This table contains two columns one that contains the original column value and the 2nd one contains the "lookup" value. You can join both tables using the Merge function of Power Query, the next to articles explain this feature in more detail:

• Merge queries overview - Power Query | Microsoft Docs
• Table.Join - PowerQuery M | Microsoft Docs

Depending on the nature of the join you can keep or remove the rows where the city is not contained in the lookup table, my recommendation is "keep". 

You can create a report(page) showing the cities that are not contained in the lookup table.

Here you will find a very little pbix file
https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/ER5HTPFrq8lOopj2IdRuQx0BAftaY...
that demonstrates what I described above. Next to "keeping"  the rows if a city is not contained in the lookup table (Left outer join), the resulting null value will be replaced.

Hopefully, this provides an idea of how to tackle this challenge.

Regards,

Tom

[monojchakrab · Accepted solution]

let me try this out @TomMartens