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.
Hey everybody,
I´m currently working on a custom connector for a api. The conector is already set up to deliver all the data in one big table but then I discovered navigation tables so I guess I have to start all over again 😊
So the concept is the following. I have one user who owns 0…n company’s which all own 0…n agents which all own 0…n devices. And each device can own 0…n Tags.
So what I would want to build is an output of nested tables which looks like this:
As mentioned the data is now being delivered in one big table. But I thing getting it in a table to drill through it would be was cooler 😊
Sadly I have no idea to get this as dynamic as I want. From my understanding of other programming languages I would do some for loops but I haven’t really figured out how to do that in m.
Any tipps how to start this right?
Thanks in advance!
Regards,
Luis
Solved! Go to Solution.
Hi @luisat ,
I think your question may be more suitable for the Developers forum: https://community.powerbi.com/t5/Developer/bd-p/Developer
As to M, I think there may be several ways to achieve what you want. The below code does literally what you want - a set of nested tables, but I am not sure about practicality in application to connectors:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcGJSrE6qKJGQJyEIgoSMQbiZAxREyBOgYvC1JkCcSqGqBkQp6GIgnSbA3E6hqgFEGcoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [User = _t, #"Company Name" = _t, #"Agent Name" = _t, #"Device Name" = _t, Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Company Name", type text}, {"Agent Name", type text}, {"Device Name", type text}, {"Data", type text}}),
#"Pack Data" = Table.Group(#"Changed Type", {"User", "Company Name", "Agent Name", "Device Name"}, {{"Data", each _}}),
#"Pack Devices" = Table.Group(#"Pack Data", {"User", "Company Name", "Agent Name"}, {{"Data", each _}}),
#"Pack Agents" = Table.Group(#"Pack Devices", {"User", "Company Name"}, {{"Data", each _}}),
#"Pack Companies" = Table.Group(#"Pack Agents", {"User"}, {{"Data", each _}})
in
#"Pack Companies"
This can be further improved by adding table types in the grouping and maybe creating a function to loop through the column to remove some duplicated code, but in principle, this does what you need (as far as I understand it).
Kind regards,
JB
Hi @luisat ,
I think your question may be more suitable for the Developers forum: https://community.powerbi.com/t5/Developer/bd-p/Developer
As to M, I think there may be several ways to achieve what you want. The below code does literally what you want - a set of nested tables, but I am not sure about practicality in application to connectors:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcGJSrE6qKJGQJyEIgoSMQbiZAxREyBOgYvC1JkCcSqGqBkQp6GIgnSbA3E6hqgFEGcoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [User = _t, #"Company Name" = _t, #"Agent Name" = _t, #"Device Name" = _t, Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Company Name", type text}, {"Agent Name", type text}, {"Device Name", type text}, {"Data", type text}}),
#"Pack Data" = Table.Group(#"Changed Type", {"User", "Company Name", "Agent Name", "Device Name"}, {{"Data", each _}}),
#"Pack Devices" = Table.Group(#"Pack Data", {"User", "Company Name", "Agent Name"}, {{"Data", each _}}),
#"Pack Agents" = Table.Group(#"Pack Devices", {"User", "Company Name"}, {{"Data", each _}}),
#"Pack Companies" = Table.Group(#"Pack Agents", {"User"}, {{"Data", each _}})
in
#"Pack Companies"
This can be further improved by adding table types in the grouping and maybe creating a function to loop through the column to remove some duplicated code, but in principle, this does what you need (as far as I understand it).
Kind regards,
JB
hey @Anonymous ,
thank you for the perfect packing example!
This is exactly what i was looking for!
Regards,
Luis
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.