Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Summarize Columns in Edit Queries

Hey guys!    I have been using a power BI for some time now, but without ever touching M language, so maybe this question is not so hard to solve. Due to size restrictions in my data export softwa...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    We don't need Crossjoin with DAX. So the performance could be reasonable. Please download the demo from the attachment. There are three solutions.

    1. DAX.

    Table =
DISTINCT (
    UNION (
        SELECTCOLUMNS (
            Table1,
            "EAN", [EAN],
            "DESC", [DESC],
            "STORE", [STORE],
            "SEMANA", [SEMANA]
        ),
        SELECTCOLUMNS (
            Table2,
            "EAN", [EAN],
            "DESC", [DESC],
            "STORE", [STORE],
            "SEMANA", [SEMANA]
        )
    )
)

    2. Using the function in the menu of the Query Editor. 

    3. Using M.

    let
    Source = Table.Combine({Table.SelectColumns(Table1, {"EAN", "DESC", "STORE", "SEMANA"}), Table.SelectColumns(Table2, {"EAN", "DESC", "STORE", "SEMANA"})}),
    #"Removed Duplicates" = Table.Distinct(Source)
in
    #"Removed Duplicates"

    Summarize-Columns-in-Edit-Queries

     

    Best Regards,
    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Anonymous,

 

We don't need Crossjoin with DAX. So the performance could be reasonable. Please download the demo from the attachment. There are three solutions.

1. DAX.

Table =
DISTINCT (
    UNION (
        SELECTCOLUMNS (
            Table1,
            "EAN", [EAN],
            "DESC", [DESC],
            "STORE", [STORE],
            "SEMANA", [SEMANA]
        ),
        SELECTCOLUMNS (
            Table2,
            "EAN", [EAN],
            "DESC", [DESC],
            "STORE", [STORE],
            "SEMANA", [SEMANA]
        )
    )
)

2. Using the function in the menu of the Query Editor. 

3. Using M.

let
    Source = Table.Combine({Table.SelectColumns(Table1, {"EAN", "DESC", "STORE", "SEMANA"}), Table.SelectColumns(Table2, {"EAN", "DESC", "STORE", "SEMANA"})}),
    #"Removed Duplicates" = Table.Distinct(Source)
in
    #"Removed Duplicates"

Summarize-Columns-in-Edit-Queries

 

Best Regards,
Dale