Reducing multiple lines to one new line in new chart with headlines from column

[Jay_Are]

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.

[dufoq3 · Accepted 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

[Jay_Are]

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.

[dufoq3]

You're welcome. Yes, in v1 I use group with some transformation inside.

In v2 I use Pivot Column but without additional transformation this would not work.