Get the values from merged and multi-layered columns/rows

[jeongkim]

Spoiler

Hi,

Can we get the values from merged and multi-layered cells using PBI Query editor or something?

The struggling points below:

1. Not all the merged cells are taken with values.

2. Grocery and Clothes should be categorized as well which is just written only in 1 cell on top of the table.

3. Second levels of columns should be categorized. If we use First row as a header then parent category Fruit/Drink will be deleted.

4. New item can be added later both column/row so this table is not fixed.

Merged cells from original sheet

Desired ouput as raw data

Please help!

P.S. Can't we attach the sheet here?

[Ahmedx · Accepted solution]

to know how to do this watch my video

https://1drv.ms/v/s!AiUZ0Ws7G26RjG9DHqweYEqkGTkC?e=mweHY2

[Ahmedx · Accepted solution]

https://community.fabric.microsoft.com/t5/Power-Query/Power-Bi-query-or-formula-to-Get-Table-name/td...

[Ahmedx · Accepted solution]

to know how to do this watch my video

https://1drv.ms/v/s!AiUZ0Ws7G26RjG9DHqweYEqkGTkC?e=mweHY2

[jeongkim]

Hi,

1 more question,

Deos it work with adding more columns(from right) and rows(from bottom)?

Or we need to modify query every time we get new columns/row? especially for Transpose/Unpivot fuction. 

[Ahmedx]

should work

[jeongkim]

Hi Ahmedx,

It's super! Thank you so much for your detailed video.

By the way, how could you get the text of 'Grocery' and 'Clothes'?

It is not always at the same row(but same column) and not only one. There are multiple 'Grocery' and 'Clothes' with multiple tables so dynamically need to get the text. 

Meaning, from certain row to row setting will not work. 

[Ahmedx]

I didn't understand what you want
you explain in detail and show a clear example

[jeongkim]

Is there any function that creating custom column from particular cell text?

e.g. if the value right next to Fx rate is 1457.3, then create custom column with "1457.3"

(this time I created manually but hope to have dynamically)

Fx rate value will be always there and only number will change every month so hope to get the value and put to all of rows by creating column. 

[Ahmedx · Accepted solution]

to know how to do this watch my video

https://1drv.ms/v/s!AiUZ0Ws7G26RjHPoKCbeYM5HQHKM?e=PcGrdS

[Ahmedx · Accepted solution]

https://community.fabric.microsoft.com/t5/Power-Query/Power-Bi-query-or-formula-to-Get-Table-name/td...