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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Syndicate_Admin
Administrator
Administrator

How to return the max number from a delimited column?

Hi all. Struggling to figure something out. I'm sure it's quite simple but can't find the best way.

 

I'm importing data from several files in a folder, files are a data dump from another system. each with the same number of rows, each row shows the status of an item on that row.

 

In two columns, data is seperated with a delimiter:

 

ItemAttribute 1Attribute 2
11, 4, 71, 3, 7
22, 4, 62, 5, 6
32, 5, 73, 5, 7

 

For each row I need to show just the highest value of the attribute in the final data. So I would want the transformed data model to look like this:

ItemAttribute 1Attribute 2
177
266
377

 

How can I achieve this?

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi @Syndicate_Admin 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUUTDRUTCHsIxBrFidaCUjIN8ILGMGYZmCWCAZYzgfpAeowQKsJxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Attribute 1" = _t, #"Attribute 2" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Max(List.RemoveNulls(
List.Transform(Text.ToList([Attribute 1]),each if Value.Is(Value.FromText(_),type number) then _ else null)))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Max(List.RemoveNulls(
List.Transform(Text.ToList([Attribute 2]),each if Value.Is(Value.FromText(_),type number) then _ else null)))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute 1", "Attribute 2"})
in
    #"Removed Columns"

Output

vxinruzhumsft_0-1704431978391.png

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

dufoq3
Super User
Super User

Hi,

dufoq3_0-1704809290348.png

  • Refer your data in 2nd Step YourData
  • If you want to add more "Attribute" columns for transformation --> you can do that in 3rd step ColumnsToTransform

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUUTDRUTCHsIxBrFidaCUjIN8ILGMGYZmCWCAZYzgfpMcYyoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Attribute 1" = _t, #"Attribute 2" = _t]),
    YourData = Source,
    ColumnsToTransform = {"Attribute 1", "Attribute 2"},
    TransformedColumns = Table.TransformColumns(YourData, 
        List.Transform(ColumnsToTransform, (colName)=> 
            {colName, each 
                Number.From(
                    List.Max(
                        List.Transform(
                            Text.Split(_, ","),
                            Text.Trim
                        )
                    )
                ), type number
            }
        )
    )
in
    TransformedColumns

 

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

4 REPLIES 4
dufoq3
Super User
Super User

Hi,

dufoq3_0-1704809290348.png

  • Refer your data in 2nd Step YourData
  • If you want to add more "Attribute" columns for transformation --> you can do that in 3rd step ColumnsToTransform

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUUTDRUTCHsIxBrFidaCUjIN8ILGMGYZmCWCAZYzgfpMcYyoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Attribute 1" = _t, #"Attribute 2" = _t]),
    YourData = Source,
    ColumnsToTransform = {"Attribute 1", "Attribute 2"},
    TransformedColumns = Table.TransformColumns(YourData, 
        List.Transform(ColumnsToTransform, (colName)=> 
            {colName, each 
                Number.From(
                    List.Max(
                        List.Transform(
                            Text.Split(_, ","),
                            Text.Trim
                        )
                    )
                ), type number
            }
        )
    )
in
    TransformedColumns

 

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Anonymous
Not applicable

Hi @Syndicate_Admin 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUUTDRUTCHsIxBrFidaCUjIN8ILGMGYZmCWCAZYzgfpAeowQKsJxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Attribute 1" = _t, #"Attribute 2" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Max(List.RemoveNulls(
List.Transform(Text.ToList([Attribute 1]),each if Value.Is(Value.FromText(_),type number) then _ else null)))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Max(List.RemoveNulls(
List.Transform(Text.ToList([Attribute 2]),each if Value.Is(Value.FromText(_),type number) then _ else null)))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute 1", "Attribute 2"})
in
    #"Removed Columns"

Output

vxinruzhumsft_0-1704431978391.png

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

olgad
Super User
Super User

Hi, if the column Attribute always stores the numbers in an ascending order, then you always need the last number, which is split by delimiter-comma, right-most delimiter.

olgad_0-1704384747755.png

 


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

This is great and so simple, but I'm not certain enough that the last number will always be the highest. Will do a check

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.