cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dynamic column based on table data

 

Good morning everyone,

 

I am such a newbie in Power Query - I literally found out it exists yesterday so apologies if what I'm trying to achieve doesn't make sense but that's exactly why I'm here :-) 

 

So straight to the point. I have the following data structure

 

 

The first, small table is the Device2Network

The table beneath is the Vlan2Network

 

The third table, in blue is the Table I am working with. The goal is to do the following:

 

Merge the Data with Device2Network so I can get a new column where I have the Device to Network relationship. 

Then I need another column to map the VLAN for that particular network. 

 

I have managed to achieve it this way:

 

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Device", type text}, {"VLAN", type any}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Device"},Device2Network,{"Device"},"Device2Network",JoinKind.LeftOuter),
    #"Expanded Device2Network" = Table.ExpandTableColumn(#"Merged Queries", "Device2Network", {"Network"}, {"Network"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Expanded Device2Network", "New VLAN", each get_new_vlan([VLAN], [Network]))
in
    #"Invoked Custom Function"

 

 

And this is my custom function

 

 

(vlan_id as number, network as text) =>
let
    Source = Vlan2Network,
    ROW = Table.SelectRows(Source, each ([VLAN] = vlan_id)),
    Result = Record.Field(ROW{0},network)
in
    Result

 

All is good and it all works fine... When I test it with just a few rows.. My problem is that I need that to work with a table which is almost 80,000 rows... And there it doesn't look great Smiley Indifferent 

 

My formula is super inefficient as it takes ages (probably about 20min. and huge amount of memory) to execute it. 

 

Can you please help me with that? 

 

Many thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dynamic column based on table data

@Anonymous I'm going to answer your question in a different way. You have the capability to do so much with both Power Query and Modeling. They are great at doing different things, though there is cross-over in certain actions.

You don't need to combine all the data into a single table in Power Query in order for it to work, and the performance will likely be better if you don't try to merge and create things where you can just create a relationship.

Here is how I would approach your issue.

I would take the VLAN2Network table, and in PQ take the following actions to pivot the data in a way that you can create a relationship in the Model.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcmxEQAgCATBXogN5F+rYei/DZ0j2WSrInesSBT626tCBAqtCRMotCcOgUKfiUug0N/uBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [VLAN = _t, NetworkA = _t, NetworkB = _t, NetworkC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"VLAN", Int64.Type}, {"NetworkA", Int64.Type}, {"NetworkB", Int64.Type}, {"NetworkC", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"NetworkC", "NetworkB", "NetworkA"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Value", "NewVLAN"}, {"Attribute", "Network"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"NewVLAN", Int64.Type}})
in
    #"Changed Type1"

then, close & apply so that you are in the "model" section. Go to the relationships tab

model.PNG

Create a relationship on network by clicking on the column and dragging and dropping to the other table column.

relationship.PNG

This will allow you to join the tables together and your output "data" table matches your example. This should be much faster than the method you are attempting doing this all in PQ

table.PNG

 

 

Near SE WI? Join our PUG Milwaukee Brew City PUG
2 REPLIES 2
Super User
Super User

Re: Dynamic column based on table data

@Anonymous I'm going to answer your question in a different way. You have the capability to do so much with both Power Query and Modeling. They are great at doing different things, though there is cross-over in certain actions.

You don't need to combine all the data into a single table in Power Query in order for it to work, and the performance will likely be better if you don't try to merge and create things where you can just create a relationship.

Here is how I would approach your issue.

I would take the VLAN2Network table, and in PQ take the following actions to pivot the data in a way that you can create a relationship in the Model.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcmxEQAgCATBXogN5F+rYei/DZ0j2WSrInesSBT626tCBAqtCRMotCcOgUKfiUug0N/uBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [VLAN = _t, NetworkA = _t, NetworkB = _t, NetworkC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"VLAN", Int64.Type}, {"NetworkA", Int64.Type}, {"NetworkB", Int64.Type}, {"NetworkC", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"NetworkC", "NetworkB", "NetworkA"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Value", "NewVLAN"}, {"Attribute", "Network"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"NewVLAN", Int64.Type}})
in
    #"Changed Type1"

then, close & apply so that you are in the "model" section. Go to the relationships tab

model.PNG

Create a relationship on network by clicking on the column and dragging and dropping to the other table column.

relationship.PNG

This will allow you to join the tables together and your output "data" table matches your example. This should be much faster than the method you are attempting doing this all in PQ

table.PNG

 

 

Near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

Re: Dynamic column based on table data

Hi Seth and many thanks for your prompt response!

 

Thank you for spending time on that, I really appreciate it.

 

Now the thing is that I have to work with these tables, because they're given. The only table I created for the sole purpose of creating relation is the Device2Network table (the smallest one). The other table (VLAN2Network) is given to us/me in this format. 

 

I was well aware I can simply transform this table into Network, VLAN, NewVLAN and then just perform a simple Join/Merge function using power query. My goal was to keep the "raw"data as is and only work with it without transforming it if that makes sense. 

 

 

That's why I ended-up writing a custom formula. 


I managed to do it with a LOOKUPVALUE DAX formula, which works much faster than mine, so that's another way of doing it, but if I can avoid mixing Power Query and DAX I would rather do. 

 

I hope that makes sense and thanks once again for your response.

 

 

P.s. I have replied much sooner to this before I even tried the proposed solution (such a rookie :-)) 

 

I have now tried your code and realised it will work with my data anyway, just have to unpivot it & then using it to create my relations ... I think that'll do it! 

 

Thank you @Seth_C_Bauer