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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors