List to column

[vishnusingh]

Hi Team,

I am fetching data from web URL and some data is showing as List. Basically these are IPs. Refer below snap

Here list is having more than one IP in List.

Now I would like to extract these IPs in a column as comma seperated.

Any leads on it.

Thanks you!

[PaulDBrown]

There is an option in the ribbon which is "convert to table"

[vishnusingh]

if I click on List I can see below two IPs in List. just want to show the value in column to end user.

[v-chenwuz-msft]

Hi @vishnusingh ,

Please try this m code:

let
    Source = Table.FromRecords({
    [ip =  "192.168.1.1"],
    [ip = Table.ToList(Table.FromRecords({[ip = "10.168.1.2"],[ip = "10.168.1.3"]}))],
    [ip = "22.168.1.4"]}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Type.Is(Value.Type([ip]),type list) then [ip] else {[ip]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vishnusingh]

Hi @v-chenwuz-msft 

We are almost close however we would require these IPs in the same column as comma/semi-colon separated not in a new column.

[v-chenwuz-msft · Accepted solution]

Hi @vishnusingh ,

This:

let
    Source = Table.FromRecords({
    [ip =  "192.168.1.1"],
    [ip = Table.ToList(Table.FromRecords({[ip = "10.168.1.2"],[ip = "10.168.1.3"]}))],
    [ip = "22.168.1.4"]}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Type.Is(Value.Type([ip]),type list) then [ip] else {[ip]}),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"ip"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "ip"}})
in
    #"Renamed Columns"

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

Sorry, what do you mean by "extract these IPs in a column"? Can you show a depiction of what you are trying to achieve?