Need help in collapsing table on itself

[EaglesTony]

Hi,

  I have the following table:

  FeatureKey      AddedOrRemovedTotal       Count

  ABC                  RemovedFromParent          12

  ABC                  AddedToParent                     5

  DEF                  RemovedFromParent             2

  IJK                    AddedToParent                      8

I need to collapse these into a single row based on FeatureKey as such:

  FeatureKey      TotalRemoved       TotalAdded

  ABC                  12                         5

  DEF                  2                           0

  IJK                    0                           8

I'm ok if this is in DAX.

[danextian]

Hi @EaglesTony 

You can use Pivot in the query editor

Here's a sample code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRCkrNzS9LTXErys8NSCxKzSsBihkaKcXqwBQ4pqSkpoTkwyVNwXIurm44NEP0enp5Y9FroRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FeatureKey = _t, AddedOrRemovedTotal = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FeatureKey", type text}, {"AddedOrRemovedTotal", type text}, {"Count", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[AddedOrRemovedTotal]), "AddedOrRemovedTotal", "Count", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"RemovedFromParent", "AddedToParent"})
in
    #"Replaced Value"

[Cookistador]

Hi @EaglesTony 

You can also reach the same results via Power Query

• Open Power Query Editor: Go to "Transform data" in Power BI Desktop.
• Select your table (FeatureTable).
• Pivot the table:
  • Select the AddedOrRemovedTotal column.
  • Go to the "Transform" tab in the Power Query ribbon.
  • Click on "Pivot Column".
  • In the "Pivot Column" dialog:
    • For "Values column", select Count.
    • Click "OK".
• Rename the resulting columns: Power Query will likely create columns named "RemovedFromParent" and "AddedToParent". You can rename these to "TotalRemoved" and "TotalAdded" respectively.

If required, you can use replace value to replace null by 0

[EaglesTony]

The only issue I can see with this is speed, as the table as close to 1MB of data and already has a group by in it.

[Cookistador]

You can also add two calculated columns

TotalAdded =

CALCULATE(

    SUM('Table'[Count])+0,

    FILTER(

        'Table',

        'Table'[AddedOrRemovedTotal] = "AddedToParent"

    )

)

TotalRemoved =

CALCULATE(

    SUM('Table'[Count])+0,

    FILTER(

        'Table',

        'Table'[AddedOrRemovedTotal] = "RemovedFromParent"

    )

)

[DataInsights · Accepted solution]

@EaglesTony,

Try this calculated table:

NewTable = 
ADDCOLUMNS (
    VALUES ( BaseTable[FeatureKey] ),
    "TotalRemoved",
        CALCULATE (
            SUM ( BaseTable[Count] ),
            BaseTable[AddedOrRemovedTotal] = "RemovedFromParent"
        ),
    "TotalAdded",
        CALCULATE (
            SUM ( BaseTable[Count] ),
            BaseTable[AddedOrRemovedTotal] = "AddedToParent"
        )
)

Add "+ 0" if you want 0 instead of blank:

NewTableWithZero = 
ADDCOLUMNS (
    VALUES ( BaseTable[FeatureKey] ),
    "TotalRemoved",
        CALCULATE (
            SUM ( BaseTable[Count] ),
            BaseTable[AddedOrRemovedTotal] = "RemovedFromParent"
        ) + 0,
    "TotalAdded",
        CALCULATE (
            SUM ( BaseTable[Count] ),
            BaseTable[AddedOrRemovedTotal] = "AddedToParent"
        ) + 0
)