How do I flatten a table onto itself bsed on TeamName

[EaglesTony]

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

[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

[Greg_Deckler · Accepted solution]

@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"

[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)

[EaglesTony]

This 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.