Summarize Columns in Edit Queries

[Anonymous]

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 software, I had to create 7 tables, one for each measure I need to watch, and then organize them in Power Querry. 

What I need now is to compile all measures into one big table.

The tables have the exact same structure, with the same first 4 column names, just like shown below:

My problem is, when some information is not available, for exemple if a store doesn't register sales for a specific item, the system simply supress the row relative to that item in that store in that week.

For this reason, I need to summarize and crossjoin the first 4 columns into a new table to make sure I don't lose any information.

I know I can do this through DAX, creating a virtual table, but I fear this will make my file extremely heavy and thus impossible to work.

Is it possible to do this in the Edit Query tool, with M language? It would create a much Lighter table to work with later on. 

Any other suggestions on how to solve this problem will also be deeply apreciated! 🙂

Thanks for the support! 

[v-jiascu-msft · Accepted solution]

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"

Best Regards,
Dale