Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 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:

image.png

image.png

 

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! 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.