Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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?
Solved! Go to Solution.
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.
Hi,
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
Hi,
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
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.
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.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
9 | |
7 | |
6 | |
6 |