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)

View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.