Forum Discussion
Dynamic nav table / dynamic table / custom connector
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
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
2 Replies
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
