Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 …
Thanks
Solved! Go to 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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@gribeiro_2021 , I think some M code may help
@ImkeF , can you help on this
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |