Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I have a table which for each occurrence of a field can have one, two or even three occurrences of another field, as shown below:
I mean, I'd like to get a column for 'Local de descarga' and to introduce the 'Local de carga' items, hereabove identified as "Source 1" and "Source 2" in columns, ie, 'Local de carga 1' and 'Local de carga 2'. The rows would be, in this example, respectively "ES15-Castellon-CLH Depot"; "Huelva Refinery" and "Castellon Refinery".
I depict below what I mean, here created with the help of Excel:
This sounds like handling arrays, but since I'm relatively new with PowerBI I have no idea on how to do this.
Help would be much appreciated.
Nelson
Solved! Go to Solution.
Try this in Power Query. Copy the code starting with GroupRows and paste into your query editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ihNzSlL1A1KTcvMSy2qVNJRcg02NNV1TiwuSc3Jyc/TdfbxUEhJLcgvUYrViVZCiBOrA6bOEKLQTLcktRhVxginjDGqTCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Local de carga" = _t, #"Local de descarga" = _t]),
GroupRows = Table.Group(
Source,
{"Local de descarga"},
{"Local de carga group", each Table.SelectColumns(_, "Local de carga")[Local de carga]}
),
ExtractValues = Table.TransformColumns(
GroupRows,
{"Local de carga group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
),
SplitLocalDeCarga = Table.SplitColumn(
ExtractValues,
"Local de carga group",
Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
{"Local de carga group.1", "Local de carga group.2", "Local de carga group.3"}
),
RenameColumns = Table.RenameColumns(
SplitLocalDeCarga,
{
{"Local de carga group.1", "Local de carga 1"},
{"Local de carga group.2", "Local de carga 2"},
{"Local de carga group.3", "Local de carga 3"}
}
)
in
RenameColumns
Result:
Sample data:
Proud to be a Super User!
Try this in Power Query. Copy the code starting with GroupRows and paste into your query editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ihNzSlL1A1KTcvMSy2qVNJRcg02NNV1TiwuSc3Jyc/TdfbxUEhJLcgvUYrViVZCiBOrA6bOEKLQTLcktRhVxginjDGqTCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Local de carga" = _t, #"Local de descarga" = _t]),
GroupRows = Table.Group(
Source,
{"Local de descarga"},
{"Local de carga group", each Table.SelectColumns(_, "Local de carga")[Local de carga]}
),
ExtractValues = Table.TransformColumns(
GroupRows,
{"Local de carga group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
),
SplitLocalDeCarga = Table.SplitColumn(
ExtractValues,
"Local de carga group",
Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
{"Local de carga group.1", "Local de carga group.2", "Local de carga group.3"}
),
RenameColumns = Table.RenameColumns(
SplitLocalDeCarga,
{
{"Local de carga group.1", "Local de carga 1"},
{"Local de carga group.2", "Local de carga 2"},
{"Local de carga group.3", "Local de carga 3"}
}
)
in
RenameColumns
Result:
Sample data:
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |