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 keeps the either positive or negative change format? I attached a photo below.

As you can see I managed to split them with formulas, but the problem is that it's treating it as text so I can't make other calculations based on this.

I'm guessing there must be a way to do this. Thanks for the help.

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"

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

9 Replies

dufoq3

Community Champion

2 years ago

Hi melo4, there are many ways.

Result:

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"

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

AlienSx

Super User

2 years ago

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnIMdlPSUTKx0DMyNTPWtdQzNTPSNtAzMzVTitWJVnJMyUxJLAYqMDYCylhaaBuagRTqGuoZGwNVxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    to_list = Table.ToList(
        Source, 
        (x) => 
            {x{0}} & 
                List.Transform(
                    Splitter.SplitTextByCharacterTransition({"0".."9"}, {"+", "-"})(x{1}),
                    Expression.Evaluate
                )
    ),
    to_tbl = Table.FromList(
        to_list, (x) => x, 
        type table 
            [Column1 = text, 
            price = number, 
            change = number, 
            #"change%" = Percentage.Type]
    )
in
    to_tbl

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. 🙂

dufoq3

Community Champion

2 years ago

melo4,

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"

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

HotChilli
Community Champion
2 years ago
"but the problem is that it's treating it as text" so you have successfully split the column already? It looks like you just need to change the data type of the column (which is straightforward right-click the column header->Change Type or use the Home tab : ->Data type:)
- melo4
  Regular Visitor
  2 years ago
  I've split the values outside of the query with combination of "LEFT" / "RIGHT" / "LEN" / "SEARCH" formulas.
  I tried changing the data type in the query just like you said, but I get an error in the cells.
  
  I also tried chaning the cell types to numbers and percentages where I have the formula, but still i always get #VALUE! errors.
  - Payeras_BI
    Solution Sage
    2 years ago
    Hi melo4 ,
    Then this is an Excel problem, not a Power Query problem.
    
    Try wrapping your Excel formula used to split the values with NUMBERVALUE like this:
    =NUMBERVALUE(yourExcelFormulaForTheSplit,".")

Forum Discussion

Split column by two criteria

9 Replies

Related content

Splitting Column values

Sequential Order column based on multiple criteria

Split a column into two separate columns

Split Columns

Column Splitting