Forum Discussion
Solved
Optimize Transpose and append operations query
Hi, I want to acheived the below expected output in Power Query ,using UI I had to transpose and do multiple append to achieve this solution, Please may I have help optimize this in M-query and a...
Hi han_rj, another solution:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrKC1R0lFCoFidaCjTN7GoGFUwOLW4ODM/zxDBNEIwjRFME7CG4OT8olSgoKEBkDAFEUZgFljSNzUlMQckBMTGYFGgQiTLMVwTnFhSWpRHDfeYQq01w+oYA6iD8DomLDWvlCphQ7RbYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]), RemovedBlankAndInput = Table.SelectRows(Source, each not (List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})) or [Column1] = "Input")), // You can probably delete this step. ReplaceBlankWithNull = Table.TransformColumns(RemovedBlankAndInput, {}, each if _ = "" then null else _), AddedIndex = Table.AddIndexColumn(ReplaceBlankWithNull, "Index", 0, 1, Int64.Type), Ad_I = Table.AddColumn(AddedIndex, "I", each if List.Contains(List.RemoveNulls(Record.ToList(Record.RemoveFields(_, {"Index"}))), "Session", (x,y)=> Text.StartsWith(x, y)) then [Index]-1 else null, Int64.Type), MergedQueryItself = Table.Join(Table.RemoveColumns(Ad_I, {"I"}), "Index", Table.SelectRows(Ad_I[[I]], each [I] <> null), "I", JoinKind.LeftOuter, JoinAlgorithm.SortMerge), FilledDown = Table.FillDown(MergedQueryItself,{"I"}), GroupedRows = Table.Group(FilledDown, {"I"}, {{"T", each [ a = Table.AddColumn(Table.PromoteHeaders(Table.FromRows(Table.ToColumns(Table.Skip(Table.RemoveColumns(_, {"Index", "I"}))))), "Planet", (x)=> List.RemoveNulls(Record.ToList(_{0})){0}?, type text), b = Table.RenameColumns(a, {Table.ColumnNames(a){0}, "Type"}) ][b], type table}}, 0), CombinedT = Table.Combine(GroupedRows[T]), ChangedType = Table.TransformColumnTypes(CombinedT,{{"Type", type text}, {"Score", type number}, {"Medal", type number}}) in ChangedType
let
// Define a function to transform each planet table
TransformPlanetTable = (planetTable as table, planetName as text) as table =>
let
// Unpivot the columns to get Session, Score, and Medal in rows
UnpivotedColumns = Table.UnpivotOtherColumns(planetTable, {"Type"}, "Session", "Value"),
// Pivot the Type column to get Score and Medal as separate columns
PivotedTable = Table.Pivot(UnpivotedColumns, List.Distinct(UnpivotedColumns[Type]), "Type", "Value"),
// Add the Planet column
AddPlanetColumn = Table.AddColumn(PivotedTable, "Planet", each planetName),
// Add the Tab_Name column
AddTabNameColumn = Table.AddColumn(AddPlanetColumn, "Tab_Name", each "Hokey"),
// Reorder columns to match the expected output
ReorderedColumns = Table.ReorderColumns(AddTabNameColumn,{"Session", "Score", "Medal", "Tab_Name", "Planet"})
in
ReorderedColumns,
// Apply the transformation to each planet table
TransformedMars = TransformPlanetTable(Mars, "Mars"),
TransformedSaturn = TransformPlanetTable(Saturn, "Saturn"),
TransformedVenus = TransformPlanetTable(Venus, "Venus"),
// Append all transformed tables into one
CombinedTable = Table.Combine({TransformedMars, TransformedSaturn, TransformedVenus})
in
CombinedTable
Review the attached pbix file, first need to define Function to convert each table, one by one.
Then, Unpivot all column expect the Type column.
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!