splitting multiple delimiter in column & find max

[AmazingTrans]

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

229-30

From the option split column, custom, can i insert more than one type of delimiter to split left and right?

After splitting, I would like to have another column that shows the max value between this two (min, & max) column.

How can i go about doing that?

[chuongtt]

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. 

[VEG_Admin]

I have a somewhat similar issue, but the CSV that I am importing appears to have multiple rows of data in each cell once imported into PBI:

Row  Column 1   Column 2  Column 3

1       Tenant A    Rent           $100.00

         Tenant A    Parking      $50.00

         Tenant A    Cleaning    $25.00

2       Tenant B    Rent           $150.00

         Tenant B    Parking      $50.00

         Tenant B    Cleaning     $10.00

I've shown three columns but there are actually about 40 of them (each with a future months payment amount).

Is there a way to have each row split into a separate line for each row, like this?

Row  Column 1   Column 2  Column 3

1       Tenant A    Rent           $100.00

2       Tenant A    Parking      $50.00

3       Tenant A    Cleaning    $25.00

4       Tenant B    Rent           $150.00

5       Tenant B    Parking      $50.00

6       Tenant B    Cleaning     $10.00

Thanks!

[MarcelBeug · Accepted solution]

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"

[timrodman]

Thank you so much for this solution @MarcelBeug it helped me a lot!