Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.