Summarize Columns in Edit Queries

Question

Hey guys!&nbsp;&nbsp;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.&nbsp;What I need now is to compile all measures into one big table.&nbsp;The tables have the exact same structure, with the same first 4 column names, just like shown below:&nbsp;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.&nbsp;For this reason,&nbsp;I need to&nbsp;summarize and crossjoin the first 4 columns&nbsp;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.&nbsp;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.&nbsp;&nbsp;Any other suggestions on how to solve this problem will also be deeply apreciated! :)Thanks for the support!&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-jiascu-msft · Accepted Answer

Hi&nbsp;Anonymous,
&nbsp;
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&nbsp;=
DISTINCT&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;UNION&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECTCOLUMNS&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table1,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"EAN",&nbsp;[EAN],
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"DESC",&nbsp;[DESC],
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"STORE",&nbsp;[STORE],
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"SEMANA",&nbsp;[SEMANA]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECTCOLUMNS&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table2,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"EAN",&nbsp;[EAN],
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"DESC",&nbsp;[DESC],
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"STORE",&nbsp;[STORE],
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"SEMANA",&nbsp;[SEMANA]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;)
)

2. Using the function in the menu of the Query Editor.&nbsp;
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"

&nbsp;
Best Regards,Dale

Forum Discussion

Summarize Columns in Edit Queries

1 Reply

Related content

Parameter in summarize

Summarize Multiple Columns

Don't Summarize....still summarizes...?

Different Groupby Column Names in Summarize

Summarize/Summarize Column Roll up