How to return the max number from a delimited column?

[Syndicate_Admin]

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:

Item	Attribute 1	Attribute 2
1	1, 4, 7	1, 3, 7
2	2, 4, 6	2, 5, 6
3	2, 5, 7	3, 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:

Item	Attribute 1	Attribute 2
1	7	7
2	6	6
3	7	7

How can I achieve this?

[v-xinruzhu-msft · Accepted solution]

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

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.

[dufoq3 · Accepted solution]

Hi,

• 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

[v-xinruzhu-msft · Accepted solution]

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

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]

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.

[Syndicate_Admin]

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