M query: Adding a column whose value depends on a category

[PivotTable]

Hello,

I'm new to m query, and would like to add a column to a table that is equal to the value for a particular row, according to a particular group, as demonstrated by the column in red in the picture. - In this case, for each group (D, E, F), the value of the new column is set to that of the indicator B2 for that group.

I want to do this within an m query that I'm using to pull in and clean tables from multiple excel workbooks.

Very grateful for advice, thanks.

[Stachu]

you can do this in the following way
1) reference your Source table, creating a new query

2) in the new query filter B2 only
3) in the source table - merge with the query from 2), based on Group&Indicator

4) expand the value
you can also do this without referencing, but it would require changing the merge references manually

[PivotTable]

Dear Stachu,

You are right that it can be done that way, but I'm looking for an alternative method because:-

1. It's not just one column that I need to add, but several. - I will go on to check for example that:
   • None of the indicators are bigger than B2
   • That B5 is not bigger than B1
   • etc.
2. The join takes a fair amount of computing power, and each extra column joined on makes the query slower and slower.

The reason I wish to do this within the m query that pulls in each excel table is because I think it should take less processing time to do this for each sheet individually, than to attempt it after they have all been merged into a single (very large) table.

Thanks,

[Stachu]

M is pretty well optimized, so I don't think there will be significant performance difference in doing it for each sheet vs on a total table, it even could be that the final refresh is faster if you do it once (not necessarily when developing it though) - the benefit is much easier maintenance

Ad1 sorry but without the concrete question I cannot give a concrete answer

Ad2 I'm working with multiple Excel tables pulled into PowerBI totaling ~8mln rows, and joins work quite well there, with reasonable performance. I think for now I'd focus on getting the intended result first and think of optimization later - if it's needed at all