Forum Discussion
EaglesTony
Post Prodigy
Post ProdigyHow do I flatten a table onto itself bsed on TeamName
Hi, I have the following table: TEAM TYPEOFWORK COUNT 1 BAU 5 1 NEW 3 2 BAU ...
EaglesTony Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJyDAWSpkqxOhC+n2s4kDQG843g8kZgvjFc3kIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TEAM = _t, TYPEOFWORK = _t, COUNT = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"TEAM", Int64.Type}, {"TYPEOFWORK", type text}, {"COUNT", Int64.Type}}), BAURows = Table.SelectRows(#"Changed Type", each ([TYPEOFWORK] = "BAU")), NewRows = Table.SelectRows(#"Changed Type", each ([TYPEOFWORK] = "NEW")), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"TYPEOFWORK", "COUNT"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns"), #"Merged Queries" = Table.NestedJoin(#"Removed Duplicates", {"TEAM"}, BAURows, {"TEAM"}, "Table", JoinKind.LeftOuter), #"Merged Queries1" = Table.NestedJoin(#"Merged Queries", {"TEAM"}, NewRows, {"TEAM"}, "Table (2)", JoinKind.LeftOuter), #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries1", "Table", {"TYPEOFWORK", "COUNT"}, {"Table.TYPEOFWORK", "Table.COUNT"}), #"Expanded Table (2)" = Table.ExpandTableColumn(#"Expanded Table", "Table (2)", {"TYPEOFWORK", "COUNT"}, {"Table (2).TYPEOFWORK", "Table (2).COUNT"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Table (2)",null,"BAU",Replacer.ReplaceValue,{"Table.TYPEOFWORK"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Table.COUNT"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,"NEW",Replacer.ReplaceValue,{"Table (2).TYPEOFWORK"}), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",null,0,Replacer.ReplaceValue,{"Table (2).COUNT"}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Value3",{{"Table.TYPEOFWORK", "BAULiteral"}, {"Table.COUNT", "BAUCOUNT"}, {"Table (2).TYPEOFWORK", "NewLiteral"}, {"Table (2).COUNT", "NEWCOUNT"}}) in #"Renamed Columns"
dufoq3
Community Champion
Community ChampionHi EaglesTony, another solution:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJyDAWSpkqxOhC+n2s4kDQG843g8kZgvjFc3kIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TEAM = _t, TYPEOFWORK = _t, COUNT = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"TEAM", Int64.Type}, {"TYPEOFWORK", type text}, {"COUNT", Int64.Type}}),
Grouped = Table.Group(ChangedType, {"TEAM"}, {{"All", each _}, {"T", each
[ a = {_{0}[TEAM]} & List.Combine(Table.ToRows(Table.RemoveColumns(_, "TEAM"))),
b = {"TEAM"} & List.Combine(List.Transform([TYPEOFWORK], (x)=> { x & "Literal", x & "COUNT" })),
c = Table.FromList({a}, (x)=> x, b)
][c], type table}}),
T = Table.Combine(Grouped[T]),
FilledDownLiteral = Table.FillDown(T, List.Select(Table.ColumnNames(T), each Text.EndsWith(_, "Literal"))),
ReplacedNulls = Table.TransformColumns(FilledDownLiteral, {}, each _ ?? 0)
in
ReplacedNulls