Forum Discussion

vishnusingh's avatar
vishnusingh
Frequent Visitor
4 years ago
Solved

List to column

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 t...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

     

vishnusingh's avatar
vishnusingh
Frequent Visitor
4 years ago

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's avatar
v-chenwuz-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
    vishnusingh
    Frequent Visitor
    4 years ago

    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's avatar
      v-chenwuz-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      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.