Split column by two criteria

[melo4]

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.

[dufoq3]

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]

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 · Accepted solution]

@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

[melo4]

Thank you! It works 🙂

[dufoq3]

You're welcome. My old query based on your sample data would not work with real data, because in real data there are multiple lines in same row (like if you use alt+enter in excel).

[AlienSx]

v3

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

[HotChilli]

"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]

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]

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,".")