Forum Discussion
Post Partisan3 columns from 1 column with 3 dimensions on M Language - Query Editor - previous row value
Hi all,
i need to manipulate on Query Editor (M Language) a base which have 3 dimensions on a column and i have to obtain 3 columns - Category, Subcategory & Items
Note: As a logical step - Category items have always null on the next row from right columns while Subcategory items have values on next row
Source:
| Category A | |||
| subcategory 1 | |||
| dasda | 1837 | 21.2 | 100 |
| sdfsd | 2311 | 26.7 | 100 |
| fsds | 1891 | 21.8 | 100 |
| fsdfs | 1687 | 19.5 | 100 |
| fsdfs | 633 | 7.3 | 100 |
| subcategory 2 | |||
| fsdfs | 4157 | 48 | 100 |
| fsdfds | 2278 | 26.3 | 100 |
| fdss | 1925 | 22.2 | 100 |
| Category B | |||
| subcategory 1 | |||
| fsdfsf | 650 | 7.5 | 100 |
| fsfs | 995 | 11.5 | 100 |
| fsds | 917 | 10.6 | 100 |
| fsdfsf | 995 | 11.5 | 100 |
| fsdfsf | 1234 | 14.3 | 100 |
expected results:
| Category A | subcategory 1 | dasda | 1837 | 21.2 | 100 |
| Category A | subcategory 1 | sdfsd | 2311 | 26.7 | 100 |
| Category A | subcategory 1 | fsds | 1891 | 21.8 | 100 |
| Category A | subcategory 1 | fsdfs | 1687 | 19.5 | 100 |
| Category A | subcategory 1 | fsdfs | 633 | 7.3 | 100 |
| Category A | subcategory 2 | fsdfs | 4157 | 48 | 100 |
| Category A | subcategory 2 | fsdfds | 2278 | 26.3 | 100 |
| Category A | subcategory 2 | fdss | 1925 | 22.2 | 100 |
| Category B | subcategory 1 | fsdfsf | 650 | 7.5 | 100 |
| Category B | subcategory 1 | fsfs | 995 | 11.5 | 100 |
| Category B | subcategory 1 | fsds | 917 | 10.6 | 100 |
| Category B | subcategory 1 | fsdfsf | 995 | 11.5 | 100 |
| Category B | subcategory 1 | fsdfsf | 1234 | 14.3 | 100 |
Can you help me with this issue
Thanks,
Cosmin
2 Replies
- vivran22
Community Champion
Hi,
Please find the pbix file here
There could be other possible ways, but this is what I have come up with and it should solve the purpose.
Rgds,
Vivek
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
cosmincHi,
on the real base the names are different and not connected with the scope, i put that names for a better understanding
i say you used them on conditional columns
the construction have to be something with if below ros is null then column otherwise null
Thanks,
Cosmin
