Forum Discussion
M query: Adding a column whose value depends on a category
Community Championyou 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
Dear Stachu,
You are right that it can be done that way, but I'm looking for an alternative method because:-
- 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.
- 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