Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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