Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |