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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Matt_H_Clam
Frequent Visitor

Converting excel connection to Dax - ie SQL to Power Query and DAX

Ive inherited a spreadsheet that connects to a local SQL database with a command string and the following command text;

 

SELECT shipmentitems.partid, shipmentitems.ordertype, invoiceitems.quantity, invoiceitems.createddate, shipments.traderid, invoiceitems.homeunitprice, partrevisions.bommethod, partrevisions.routingmethod, allpartmaster.sm, orderitems.vatcode, allpartmaster.fsunit, invoiceitems.currencyunitprice, allpartmaster.casting, invoices.taxdate, invoices.status, allpartmaster.fsmat, allpartmaster.fssubcon
FROM efacdb.dbo.allpartmaster allpartmaster, efacdb.dbo.invoiceitems invoiceitems, efacdb.dbo.invoices invoices, efacdb.dbo.orderitems orderitems, efacdb.dbo.partrevisions partrevisions, efacdb.dbo.shipmentitems shipmentitems, efacdb.dbo.shipments shipments
WHERE orderitems.orderid = shipmentitems.orderid AND shipmentitems.shipmentid = shipments.id AND orderitems.itemnumber = shipmentitems.orderitemnumber AND invoiceitems.shipmentid = shipmentitems.shipmentid AND shipmentitems.itemnumber = invoiceitems.shipmentitemnumber AND partrevisions.partid = invoiceitems.partid AND partrevisions.partid = orderitems.partid AND partrevisions.partid = shipmentitems.partid AND partrevisions.partrevisionid = orderitems.partrevisionid AND partrevisions.partrevisionid = shipmentitems.partrevisionid AND invoiceitems.partid = allpartmaster.partnum AND invoiceitems.invoiceid = invoices.id AND ((shipmentitems.ordertype='SO'))
ORDER BY shipmentitems.partid, shipments.traderid, invoiceitems.createddate

 

 

I wish to recreate as a data model in PowerBI desktop then publish to the service using our gateway.

 

So far in desktop I have created a connection to the database in Power Query and imported the relevant tables no problem (with filters to limit data to the last 12 months)

 

I have also created 1 to many relationships where the above command says a field in one table equals one in another or created a master table for fields with a many to many.  This covers the FROM and WHERE

 

Being a novice here with the Select part of the command im looking to create a new table in the model using DAX and my  thought process was to use;

 

New Table = SUMMARIZECOLUMNS(

                                                             shipmentitems[part.id], shipmentitems[ordertype], invoiceitems[quantity], shipments[traderid] etc etc 

)

 

 

the new table is hanging when i press enter and i just get the 'im working on it' message.

 

Is there a better way? or have i fundamentally got it right/wrong?

 

 

 

2 REPLIES 2
amitchandak
Super User
Super User

@Matt_H_Clam , if data is really big, then having a dax table is not a good idea.  It can also issues because of system performance.  Check RAM and Hard disk availability

 

Try to bring this from DB, in not try in power query

Thanks. I am using 20%CPU and 4GB of memory on my laptop as its trying to run.. I have 32GB RAM and a Core i7 2.8ghz Processsor so its pretty meaty

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.