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?
