Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello community,
first of all - I am bloody beginner and testing a few days with several sources...but the actual problem gets me really bad:
I have a table with one fulfilled column (a ID is listed multiple times) and behind it columns with labels/values not everytime filled.
Out of this I want to create a new "straight" table - as seen followed screens.
to this:
A few solutions were "near" it, but I might be too new to transfer it...I am happy on any input.
Solved! Go to Solution.
Hi @Jay_Are, try this, but for future requests provide sample data as table so we can copy/paste and expected result (based on sample data)
Result
v1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY+7CsJAEEX/ZesQ9qlpfRQ2imAZUoxmwYDZyD4E/Xp3o0QzSTNz4R6GOWVJmBBSKq5IRk4efHD/AS6+eZAqG2Fb8PqzQhs3ZTktck7pEoOHLg3bM1xIhftVXVvtXJ9SSFfX2t4ag8mjtq4z4+C1xdimC8anc+YF11sMXHwRpRacsVnHO7jBccBmHGV0jB0Cf45CFAVu5wx30J6DrTE6VdyDfWJqYpg+qt4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [result.id = _t, result.metadata.name = _t, result.metadata.label = _t, result.metadata.value = _t]),
RemovedColumns = Table.RemoveColumns(Source,{"result.metadata.name"}),
GroupedRows = Table.Group(RemovedColumns, {"result.id"}, {{"All", each Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(_, {"result.id"}, MissingField.Error))), type table}}),
ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", List.Distinct(RemovedColumns[result.metadata.label]))
in
ExpandedAll
v2
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY+7CsJAEEX/ZesQ9qlpfRQ2imAZUoxmwYDZyD4E/Xp3o0QzSTNz4R6GOWVJmBBSKq5IRk4efHD/AS6+eZAqG2Fb8PqzQhs3ZTktck7pEoOHLg3bM1xIhftVXVvtXJ9SSFfX2t4ag8mjtq4z4+C1xdimC8anc+YF11sMXHwRpRacsVnHO7jBccBmHGV0jB0Cf45CFAVu5wx30J6DrTE6VdyDfWJqYpg+qt4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [result.id = _t, result.metadata.name = _t, result.metadata.label = _t, result.metadata.value = _t]),
RemovedColumns = Table.RemoveColumns(Source,{"result.metadata.name"}),
PivotedColumn = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[result.metadata.label]), "result.metadata.label", "result.metadata.value", each _),
Transform = Table.TransformColumns(PivotedColumn, List.Transform(List.Distinct(RemovedColumns[result.metadata.label]), (colName)=> { colName, each _{0} }))
in
Transform
Hi @Jay_Are, try this, but for future requests provide sample data as table so we can copy/paste and expected result (based on sample data)
Result
v1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY+7CsJAEEX/ZesQ9qlpfRQ2imAZUoxmwYDZyD4E/Xp3o0QzSTNz4R6GOWVJmBBSKq5IRk4efHD/AS6+eZAqG2Fb8PqzQhs3ZTktck7pEoOHLg3bM1xIhftVXVvtXJ9SSFfX2t4ag8mjtq4z4+C1xdimC8anc+YF11sMXHwRpRacsVnHO7jBccBmHGV0jB0Cf45CFAVu5wx30J6DrTE6VdyDfWJqYpg+qt4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [result.id = _t, result.metadata.name = _t, result.metadata.label = _t, result.metadata.value = _t]),
RemovedColumns = Table.RemoveColumns(Source,{"result.metadata.name"}),
GroupedRows = Table.Group(RemovedColumns, {"result.id"}, {{"All", each Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(_, {"result.id"}, MissingField.Error))), type table}}),
ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", List.Distinct(RemovedColumns[result.metadata.label]))
in
ExpandedAll
v2
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY+7CsJAEEX/ZesQ9qlpfRQ2imAZUoxmwYDZyD4E/Xp3o0QzSTNz4R6GOWVJmBBSKq5IRk4efHD/AS6+eZAqG2Fb8PqzQhs3ZTktck7pEoOHLg3bM1xIhftVXVvtXJ9SSFfX2t4ag8mjtq4z4+C1xdimC8anc+YF11sMXHwRpRacsVnHO7jBccBmHGV0jB0Cf45CFAVu5wx30J6DrTE6VdyDfWJqYpg+qt4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [result.id = _t, result.metadata.name = _t, result.metadata.label = _t, result.metadata.value = _t]),
RemovedColumns = Table.RemoveColumns(Source,{"result.metadata.name"}),
PivotedColumn = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[result.metadata.label]), "result.metadata.label", "result.metadata.value", each _),
Transform = Table.TransformColumns(PivotedColumn, List.Transform(List.Distinct(RemovedColumns[result.metadata.label]), (colName)=> { colName, each _{0} }))
in
Transform
Great, looks really good at the moment. So you group the columns and expand the lines to separate columns. I will check it later, but thanks a lot.