Forum Discussion
M query: Adding a column whose value depends on a category
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.
3 Replies
- Stachu
Community Champion
you can do this in the following way
1) reference your Source table, creating a new query2) in the new query filter B2 only
3) in the source table - merge with the query from 2), based on Group&Indicator4) expand the value
you can also do this without referencing, but it would require changing the merge references manuallyDear 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
- It's not just one column that I need to add, but several. - I will go on to check for example that:
