cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AmazingTrans
Regular Visitor

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

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?

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
chuongtt
Regular Visitor

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
Frequent Visitor

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
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors