power query - sumifs - matrix

i have the below table in power query.

╔══════════════════════╦══════════════════════╦══════════════════╗
║       Project        ║      Project 2       ║  Balance         ║
╠══════════════════════╬══════════════════════╬══════════════════╣
║ (088) GARAJ-16       ║ (084) MFK DOMODEDOVA ║     83,434.11    ║
║ (088) GARAJ-16       ║ (085) FILI RESIDENCE ║  5,936,348.56    ║
║ (085) FILI RESIDENCE ║ (084) MFK DOMODEDOVA ║ -3,516,805.40    ║
║ (085) FILI RESIDENCE ║ (088) GARAJ-16       ║ -4,436,348.56    ║
║ (084) MFK DOMODEDOVA ║ (085) FILI RESIDENCE ║  4,516,805.40    ║
║ (084) MFK DOMODEDOVA ║ (088) GARAJ-16       ║    -83,434.11    ║
╚══════════════════════╩══════════════════════╩══════════════════╝

the result i want to get is :

╔══════════════════════╦═════════════════╦══════════════════════╦══════════════════════╗
║                      ║ (088) GARAJ-16  ║ (085) FILI RESIDENCE ║ (084) MFK DOMODEDOVA ║
╠══════════════════════╬═════════════════╬══════════════════════╬══════════════════════╣
║ (088) GARAJ-16       ║  $-             ║  $1,500,000.00       ║  $-                  ║
║ (085) FILI RESIDENCE ║  $1,500,000.00  ║  $-                  ║  $1,000,000.00       ║
║ (084) MFK DOMODEDOVA ║  $-             ║  $1,000,000.00       ║  $-                  ║
╚══════════════════════╩═════════════════╩══════════════════════╩══════════════════════╝

this result i can get by two sumifs formulas in excel but i couldnt manage to get the result directly in power query without writing any formulas.

any ideas ?

Solution

    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "fnButunSantiyeler", each fnButunSantiyeler([Query])),
    #"Expanded fnButunSantiyeler" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnButunSantiyeler", {"Hesap No", "santiye kodu", "Ruble Balance", "USD Balance"}, {"Hesap No", "santiye kodu", "Ruble Balance", "USD Balance"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded fnButunSantiyeler",{"Query"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"santiye kodu"},#"Projects List",{"Proje Kodu"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Proje Adi"}, {"Proje Adi.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"Proje Adi.1", "Karsi Proje"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Proje Kodu", "Proje Adi", "Hesap No", "santiye kodu", "Karsi Proje", "Ruble Balance"}),
    #"Added Custom2" = Table.AddColumn(#"Reordered Columns", "Proje", each "(" & [Proje Kodu] & ") " &[Proje Adi]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Karsi Proje.1", each "(" & [santiye kodu] & ") " & [Karsi Proje]),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom3",{"Karsi Proje.1", "Proje", "Proje Kodu", "Proje Adi", "Hesap No",  "santiye kodu", "Karsi Proje", "Ruble Balance"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"Proje Kodu", "Proje Adi", "santiye kodu", "Karsi Proje"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Karsi Proje.1", "Karsi Proje"}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns1",{"Proje", "Karsi Proje", "Ruble Balance"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns2", {"Proje", "Karsi Proje", "Hesap No"}, {{"Ruble Balance", each List.Sum([Ruble Balance]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Karsi Proje] <> null),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if [Hesap No] = "331" then [Proje]&"xxx"&[Karsi Proje] else if [Hesap No] = "131" then [Karsi Proje]&"xxx"&[Proje] else null ),
    #"Grouped Rows1" = Table.Group(#"Added Conditional Column", {"Custom"}, {{"sum", each List.Sum([Ruble Balance]), type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows1","Custom",Splitter.SplitTextByDelimiter("xxx", QuoteStyle.Csv),{"Custom.1", "Custom.2"}),
    #"Rounded Off" = Table.TransformColumns(#"Split Column by Delimiter",{{"sum", each Number.Round(_, 2), type number}}),
    #"Sorted Rows" = Table.Sort(#"Rounded Off",{{"Custom.2", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Custom.2]), "Custom.2", "sum", List.Sum)
in
    #"Pivoted Column"

i have solved it in another way, i put it here, maybe it may help to someone else...