Forum Discussion
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 2
3 NEW 8
I need to "flatten" it so it looks like(I'm ok if it is a new table):
TEAM BAULiteral BAUCOUNT NEWLiteral NEWCOUNT
1 BAU 5 NEW 3
2 BAU 2 NEW 0
3 BAU 0 NEW 8
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"
6 Replies
- Greg_Deckler
Community Champion
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"
EaglesTonyThis worked..thanks
- EaglesTony
Thanks, so I guess the only thing I need to change it the source ?
- Greg_Deckler
EaglesTony Correct, you could create a new query that simply connects to the data source. Then just swap out the Source line in Advanced Editor. There may also be a Navigation step and you may or may not need the Changed Type step in the code.
- EaglesTony
Here is a cavet to this, for one Team, I don't have NEW, so this doesn't give me 0 for a Team without NewRows.
= Table.NestedJoin(NewRows, {"TEAM"}, BAURows, {"TEAM"}, "Table", JoinKind.LeftOuter)
- dufoq3
Hi 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
