Extract second table data based on results from first query

[Alphonso]

Hi

I am trying to extract data from Business Central SaaS.  I need to get all entries from table "Sales_Quotes", which I am able to.  I then want to add the inventory total from the "Item_Card" table, but only relating to those items being used on the quote lines.  (If I don't restrict the inventory to those few items only, my Item_Card table extract takes around 90 seconds to refresh each time (I believe it recalulates the inventory totals from the "Item_Ledger_Table" each time it is refreshed, and that is a big old table.

I can either total up the inventory directly from the "Item_Ledger_Table" or off of the "Item_Card", but I only want to do it for an item that is in the "Sales_Quotes" lines, so that I have a report that produces infomration very quickly.

In SQL , I would simply

Select sq.*,

           ic.Inventory

from Sales_Quotes sq

LEFT JOIN Item_Card ic

on sq.Item_No = ic.No

I would be grateful for any suggestions please.

Thanks in advance

Simon

[smpa01]

@Alphonso  were you hoping for a PQ that is equivalent of the following SQL

Select sq.c1,sq.c2,sq.c3,sq.c4....sq.cn

           SUM(ic.Inventory)

from Sales_Quotes sq

INNER JOIN Item_Card ic

on sq.Item_No = ic.No
GROUP BY sq.c1,sq.c2,sq.c3,sq.c4....sq.cn

[Alphonso]

This is exactly what I am after, yes.

[smpa01 · Accepted solution]

@Alphonso  if you have two tables like following

|   Sales_Quotes    |
|--------------|----|
| Item_No      | ID |
|--------------|----|
| 1            | 1  |
| 2            | 2  |
| 3            | 3  |
| 4            | 4  |
| 5            | 5  |
| 6            | 6  |
| 7            | 7  |
| 8            | 8  |
| 9            | 9  |
| 10           | 10 |


|     Item_Card         |
|-----------|-----------|
| No        | Inventory |
|-----------|-----------|
| 2         | 100       |
| 2         | 200       |
| 3         | 300       |
| 3         | 400       |
| 4         | 500       |
| 4         | 600       |
| 4         | 700       |

the following SQL

SELECT a.item_no,
       a.id,
       Sum(b.inventory) AS Inventory
FROM   Sales_Quotes a
       INNER JOIN Item_Card b
               ON a.item_no = b.no
GROUP  BY a.item_no, a.id 

will result in this

| Item_No | ID | Inventory |
|---------|----|-----------|
| 2       | 2  | 300       |
| 3       | 3  | 700       |
| 4       | 4  | 1800      |

The equivalent PQ is following

let
   Sales_Quotes = let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApCWYNDRQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item_No = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item_No", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "ID", 1, 1, Int64.Type)
in
    #"Added Index",
    Item_Card = let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTI0MFCK1YGwjaBsYyDbGIltAmWbANmmSGwzJLY5iB0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Inventory = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Inventory", Int64.Type}})
in
    #"Changed Type",
    Source = Table.RenameColumns(let
    t1 = let
        t2 = Table.PrefixColumns(Sales_Quotes, "a"),
        t3 = Table.PrefixColumns(Item_Card, "b"),
        t4 = Table.Join(t2, {"a.Item_No"}, t3, {"b.No"}, JoinKind.Inner, null)
    in
        t4,
    t5 = Table.Group(t1, {"a.Item_No", "a.ID"}, {{"Inventory", (t6) => List.Sum(t6[b.Inventory])}}),
    t7 = Table.SelectColumns(t5, {"a.Item_No", "a.ID", "Inventory"})
in
    t7, {{"a.Item_No", "Item_No"}, {"a.ID", "ID"}, {"Inventory", "Inventory"}})
in
    Source

[Alphonso]

Hi 

The output looks like it will deliver my solution.  I am using OData to extract the table info.  Do I "just" exchange the Source From json query to OData, or is there a different form of wording required to extract OData, please?

[smpa01]

@Alphonso  my initial thoughts were that ypu were looking for a equivalent PQ of the SQL above for the tables you btouht over to PQ.

Were you meaning to run this SQL in Odata?

[Alphonso]

Sorry, I perhaps didn't explain myself fully.  I am using Business Central SaaS, which does not paly nicely with SQL/SSMS, so I am trying to run the SQL equivalent query in Power BI/Power Query.

To get at the table datain PBI/PQ, I have to get the table through OData.

Looking at my PBI Qeury in advanced editor actualy gives me the let source = structure, so i will try mixing that with your suggested solution and see if I can get the report drawn out.

Ideally, my preferred solution is to get it all through PQ in Excel.

I need to leave my desk for a few hours so will see if i can cobble the solution together and will uodate this post afterwards.

thanks for the suggestions/advice so far.

Regards

Simon

[edhans]

You are going to have to provide some data. I cannot tell if you are just trying to create a measure, or if you are trying to create a new table in Power Query (this is the PQ forum) or what.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Alphonso]

• As answered elsewhere, I am attempting to use PQ to pull all of the rows from the smaller table (Sales_Quotes) and one column from the larger one (Item_Card) here the inventory relates to items selected on the sales quotes.

I believe that this will generate the quickest report.