split rows

[gribeiro_2021]

Hello,

I have the following financial data exported to power bi:

Food and Beverage
Título	876200/1 - Brico
Título	2721741/1 - Pama
Título	804008/1 - Citro
Título	1365399/1 - Pépsico
Durable goods
Título	1/1 - Gabriel
Association and Newspapers
Título	783/1 - Escritório
Título	811/1 - Escritório
Other Miscellaneous Expenses
Título	8999985/1 - Kalunga S.A. KALUNGA
Vacation
Título	686/1 - Nubia De Sousa Veras
Título	687/1 - Adson Fortunato Pires

I need to look like this:

Título	876200/1 - Brico Bread Alimentos LTDA	food and bevare
Título	2721741/1 - Pama Comercio de Gêneros	food and bevare
Título	804008/1 - Citro Cardilli Comercio	food and bevare
Título	1365399/1 - Pépsico	food and bevare
Título	1/1 - Gabriel	durable goods
Título	783/1 - Escritório Central de	Association and Newspapers
Título	811/1 - Escritório Central de	Association and Newspapers
Título	8999985/1 - Kalunga S.A. KALUNGA	Other Miscellaneous Expenses
Título	686/1 - Nubia De Sousa Veras	Vacation
Título	687/1 - Adson Fortunato Pires	Vacation

Any suggestions? 

I have found a similar case but in my situation it does not have a type o pattern defined …

 https://www.poweredsolutions.co/2019/12/19/split-segment-partition-section-your-table-in-power-bi-po...

Thanks

[ImkeF · Accepted solution]

Hi @gribeiro_2021 ,

please paste this code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZG9TsMwFIVf5SpzKflpE2dM6c9QCJVKu0QZbpKrYCnEke0Aj8PKwMQbkBfDcsWSFG9X53w+x75Z5myFqADbClb0ShJrcmaOk88y52n40n0jzMii0HfdWw9uYCV5KUayH/letPCsfsAXHNPuwnWZVe+4luLnY2TwgnAZxPGFHz479Rex7iUWDUFtKqpprUviDgvJqbFaopQoOWouWvuklN5Uhx3JK3TEAstvVCm5Hr4lnzZjnveP51E/k4QHrkpqGmxJ9Ao27x21iq5ksdgctrR37bHp2xrhOE/msE/uT+kusfYzlrb5FA9ZaNG0LzjCmuBo4hDOZl1qYo2sNamU+YOtkLpvUQs4cGmK5fkv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column2] = null or [Column2] = "" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column2] <> ""))
in
    #"Filtered Rows"

[gribeiro_2021]

Looks good @ImkeF ! I just have another question since I didn´t quite understand how did you get the decompression and from where...

The file that i have is a much larger data and not static... I was wondering how can i reference my particular data... The example that I gave above was just to illustrate the problem concept ... Now, the solution works just fine but I cannt figure it out how to reference my data in the code provided

[ImkeF]

Great!

To apply it to your data, there is a link in my signature.

Here you can find a video describing different use cases: Power BI Forum Help: How to integrate M-code into ... - Microsoft Power BI Community 

[amitchandak]

@gribeiro_2021 , I think some M code may help

@ImkeF , can you help on this