topic split rows in DAX Commands and Tips

split rows

gribeiro_2021 — Wed, 21 Apr 2021 15:43:50 GMT

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-power-query/

Thanks

Re: split rows

amitchandak — Wed, 21 Apr 2021 17:01:52 GMT

gribeiro_2021 , I think some M code may help

ImkeF , can you help on this

Re: split rows

ImkeF — Thu, 22 Apr 2021 08:33:52 GMT

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"

Re: split rows

gribeiro_2021 — Tue, 27 Apr 2021 14:01:49 GMT

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

Re: split rows

ImkeF — Tue, 27 Apr 2021 20:30:55 GMT

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