3 columns from 1 column with 3 dimensions on M Language - Query Editor - previous row value

[cosminc]

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

[vivran22]

@cosminc 

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.

[cosminc]

Hi,

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