Solved: Transposing Tables

kburata · ‎05-04-2021

Hi,

Need to convert table from this

Key	Value
Rank	17.5309867
DocId	701135570498039
Title	Appointment
Author	Dylan
Rank	17.5309867
DocId	7011355705149
Title	Meet
Author	Steph

to this

Rank	DocId	Title	Author
17.5309867	701135570498039	Appointment	Dylan
17.5309867	7011355705149	Meet	Steph

Any help would be much appreciated.

Regards,

CNENFRNL · ‎05-05-2021

As long as each bracket of rows starts with "Rank", you might want to try this solution,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkrMy1bSUTI01zM1NrC0MDNXitWJVnLJT/ZMAQqbGxgaGpuamhuYWFoYGFuC5UIyS3JSgXKOBQX5mXklual5JYcWgGUcS0sy8ouAUi6VOYl5YCEijTc1NEE13Dc1tQTVzOCS1IIMpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Value = _t]),
    Grouped = Table.RemoveColumns(Table.Group(Source, "Key", {"ar", each Table.PromoteHeaders(Table.Transpose(_))}, 0, (x,y) => Number.From(y="Rank")), {"Key"}),
    #"Expanded ar" = Table.ExpandTableColumn(Grouped, "ar", {"Rank", "DocId", "Title", "Author"}, {"Rank", "DocId", "Title", "Author"})
in
    #"Expanded ar"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

CNENFRNL · ‎05-05-2021

As long as each bracket of rows starts with "Rank", you might want to try this solution,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkrMy1bSUTI01zM1NrC0MDNXitWJVnLJT/ZMAQqbGxgaGpuamhuYWFoYGFuC5UIyS3JSgXKOBQX5mXklual5JYcWgGUcS0sy8ouAUi6VOYl5YCEijTc1NEE13Dc1tQTVzOCS1IIMpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Value = _t]),
    Grouped = Table.RemoveColumns(Table.Group(Source, "Key", {"ar", each Table.PromoteHeaders(Table.Transpose(_))}, 0, (x,y) => Number.From(y="Rank")), {"Key"}),
    #"Expanded ar" = Table.ExpandTableColumn(Grouped, "ar", {"Rank", "DocId", "Title", "Author"}, {"Rank", "DocId", "Title", "Author"})
in
    #"Expanded ar"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!