Forum Discussion

AmazingTrans's avatar
AmazingTrans
Regular Visitor
9 years ago
Solved

splitting multiple delimiter in column & find max

Hi there, I am new with power bi.    I have a column of data of numbers but currently it is text since it got imported from csv. It has all sort of delimiters. For example : 123&456 223/553 2...
Tutorial Requests
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    It's not completely clear what you are trying to achieve; I assume that each value has 2 numbers that are separated by a delimiter, and you want the left and right numbers in separate columns.

     

    The menu-option won't let you enter multiple delimiters, but you can enter one delimiter and then slightly adjust the generated code: I changed:
    Splitter.SplitTextByDelimiter to Splitter.SplitTextByAnyDelimiter

    the delimiter by a list of delimiters (comma separated and surrounded by curly brackets {}

     

    let
    Source = #table(type table[Data = text],List.Zip({{"123&456","223/553","229-30"}})),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Data", Splitter.SplitTextByAnyDelimiter({"&","/","-"}, QuoteStyle.Csv), {"Data.1", "Data.2"})
in
    #"Split Column by Delimiter"
chuongtt's avatar
chuongtt
Regular Visitor
5 years ago

Hi MarcelBeug , 

I am a new user in power query, I have a table with the column (date) that has a different format, such as 01.04.2020 or 01/04/2020. Therefore, I would like to use a split delimiter to slit this out and combine it by try using your guide
But I had a problem: 

Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]

This is my query: = Table.SplitColumn(MonthlySalesReport, "Invoice Date", Splitter.SplitTextByDelimiter({".", "/"},QuoteStyle.Csv), {"Day", "Month" , "Year"})

 

Thanks 

P/s: I already changed the format of the Date Column is the text.