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 to extract these IPs in a column as comma seperated.

 

Any leads on it.

 

Thanks you!

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.

     

6 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

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

  • 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
      Community 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.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

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