Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
luisat
Frequent Visitor

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.

 

image.png

 

So what I would want to build is an output of nested tables which looks like this:

image.png

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

 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors