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 achieve the solution
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
3 Replies
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 ChangedTypelet
// 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
CombinedTableReview 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!!Hi han_rj,
I wanted to check if you had the opportunity to review the information provided by community members. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a Kudos so other members can easily find it.
Thanks for using Microsoft Fabric Community Forum.
