Comma delimited fields split combine and filter in power query.

[AdamWhittaker]

Hello, I am trying to figure out a way to split two fields via a comma, then add an index to each one then filter the first field to get one result and retrieve the same index from the 2nd command and then add the result of the 1st and 2nd column to new columns without effecting (duplicating the original list).

I have tried loads of different ways (like split list, addindexcolumn with select rows but it always fails on the 2nd part.

I have two columns.. IPAddress and MacAddress:

Example:

IPAddress

"127.0.0.1, 10.12.34.56, 169.254.23.56"

MacAddress

"12-FE-23-6D-14-A6,13-FE-23-6D-14-A6,14-FE-23-6D-14-A6"

Each IP address has an associated MAC and they follow the same order so 10.12.34.56's mac is 3-FE-23-6D-14-A6.

What i want to do is get the real IP and the MAC that belongs to that real IP and we can obviously get the IP by getting rid of the others with [IPAddress] <> "127.0.0.1" and [IPAddress] <> "192.168.0.1" and Text.Start([IPAddress], 😎 <> "169.254."). This would normally leave me with 1 ip address.

How would i go about getting the associated MAC? In my head i would write this to a temp table that becomes:

IP Address,        MAC,                              Index

127.0.0.1            12-FE-23-6D-14-A6       0

10.12.34.56        13-FE-23-6D-14-A6       1

169.254.23.56     14-FE-23-6D-14-A6      2

Then filter for the right ip get its index and then get the associated max via the index number.

Thanks for any help.

[AdamWhittaker]

Thanks @ThxAlot 

[ThxAlot · Accepted solution]

let
    IP = "127.0.0.1, 10.12.34.56, 169.254.23.56",
    Mac = "12-FE-23-6D-14-A6,13-FE-23-6D-14-A6,14-FE-23-6D-14-A6",
    #"To Table" = Table.FromColumns(List.Accumulate({IP, Mac}, {}, (s,c) => s & {List.Transform(Text.Split(c, ","), Text.Trim)}), {"IP","Mac"}),
    #"Added Index" = Table.AddIndexColumn(#"To Table", "Index", 0, 1, Int64.Type)
in
    #"Added Index"