Forum Discussion

melo4's avatar
melo4
Regular Visitor
2 years ago
Solved

Split column by two criteria

Hi,   I am taking German equity data from investing.com and the price, change and change% are aggregated in one column. How do I split these three numbers based on two criteria (+ and -) so that it...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    melo4,

     

    v1

    let
    Source = Web.Page(Web.Contents("https://www.investing.com/equities/germany")),
    Data = Source{0}[Data],
    Transform = Table.TransformColumns(Data, List.Transform(Table.ColumnNames(Data), (colName)=>
        {colName, each Text.Combine(List.Transform(Text.Split(_, "#(lf)"), (x)=> Text.Trim(x, {" ", "#(lf)", "#(cr)"}) ), " "), type text})),
    #"Added Custom" = Table.AddColumn(Transform, "Custom", each Text.Combine(List.Transform(Splitter.SplitTextByCharacterTransition((x)=> not List.Contains({"+", "-"}, x), (y)=> List.Contains({"+", "-", "#(lf)", "#(cr)"}, y))([Column2]), (x)=> Text.Trim(x, {" ", "#(lf)", "#(cr)"})), "|"), type text),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Price", "Change", "Change %"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Price", type number}, {"Change", type number}, {"Change %", Percentage.Type}}, "en-US")
in
    #"Changed Type"

     

    v2

    let
    Source = Web.Page(Web.Contents("https://www.investing.com/equities/germany")),
    Data = Source{0}[Data],
    Transform = Table.TransformColumns(Data, List.Transform(Table.ColumnNames(Data), (colName)=>
        {colName, each Text.Combine(List.Transform(Text.Split(_, "#(lf)"), (x)=> Text.Trim(x, {" ", "#(lf)", "#(cr)"}) ), " "), type text})),
    Ad_List = Table.AddColumn(Transform, "List", each Splitter.SplitTextByCharacterTransition((x)=> not List.Contains({"+", "-"}, x), (y)=> List.Contains({"+", "-"}, y))([Column2]), type list),
    Ad_Table = Table.AddColumn(Ad_List, "Table", each 
       List.Accumulate(
           List.Zip({ {0..List.Count([List])-1}, {"Price", "Change", "Change %"} }),
           #table(type table[Column1=text], {{[Column1]}}),
           (s,c)=> Table.AddColumn(s, c{1}, (x)=> Number.From([List]{c{0}}, "en-US"), if c{1} = "Change %" then Percentage.Type else type number)
       ), type table),
    Combined = Table.Combine(Ad_Table[Table])
in
    Combined
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi melo4, there are many ways.

 

Result:

 

 

v1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrHQMzI1M9a11DM1M9I20DMzNVOK1YlWMjYCClhaaBuageR1DfWMjYESsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(Splitter.SplitTextByCharacterTransition((x)=> not List.Contains({"+", "-"}, x), (y)=> List.Contains({"+", "-"}, y))([Column1]), "|"), type text),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Price", "Change", "Change %"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Price", type number}, {"Change", type number}, {"Change %", Percentage.Type}}, "en-US")
in
    #"Changed Type"

 

v2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrHQMzI1M9a11DM1M9I20DMzNVOK1YlWMjYCClhaaBuageR1DfWMjYESsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_List = Table.AddColumn(Source, "List", each Splitter.SplitTextByCharacterTransition((x)=> not List.Contains({"+", "-"}, x), (y)=> List.Contains({"+", "-"}, y))([Column1]), type list),
    Ad_Table = Table.AddColumn(Ad_List, "Table", each 
       List.Accumulate(
           List.Zip({ {0..List.Count([List])-1}, {"Price", "Change", "Change %"} }),
           #table(type table[Column1=text], {{[Column1]}}),
           (s,c)=> Table.AddColumn(s, c{1}, (x)=> Number.From([List]{c{0}}, "en-US"), if c{1} = "Change %" then Percentage.Type else type number)
       ), type table),
    Combined = Table.Combine(Ad_Table[Table])
in
    Combined

 

melo4's avatar
melo4
Regular Visitor
2 years ago

Thank you both for the help. dufoq3 AlienSx 

 

If I copy all three versions of your solutions in a blank query it loads the first two lines of the table, so I see that your approaches could work. However, once I change the source in the code I get all kind of errors.

 

My source in the original query is:

= Web.Page(Web.Contents("https://www.investing.com/equities/germany"))

 

I need it to be an active link to investing.com to avoid manually downloading and refreshing the data I need.

How would the code change in this instance? 

 

 

Thanks again! Much appreciated. 🙂