merging queries

[sshweky]

I need help! I have been troubleshootiong this for hours! I have 2 tables: Inventory with 11,597 records & Invaging with 2044 records and I am doing a left outer join to merge the results from invaging into the inventoy table. Here's what's happening:

(1) When I check my selection on the merge screen, it finds 10,885 of 11,597 matches. How is thast possible if the 2nd table only has 2044 records?

(2) Once the query loads, it only loads exactly 1,000 records??

What am I missing? What should I look for? I ran the merge in Access and it worked fine so I think it's not the data?

Suggestions??

Steven

[Greg_Deckler]

Well, 11,597 - 10,885 = 712. So, 712 records from your first table do not match anything in your second table. That seems straight-forward to me.

As for 1,000 records only, would have to know the data sources and perhaps paste in your query code from Advanced Editor.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[sshweky]

Thanks Greg, but each table has unique values only so it can't be that there are 10k matching records when there are only 2404 records in the whole 2nd table? Below is my code for the query. I bold face the merge where things go wrong. Thank you!

let
    Source = Access.Database(File.Contents("C:\Users\Steven\Desktop\Dropbox (Personal)\FC\dashboard\fc.mdb")),
    _inven = Source{[Schema="",Item="inven"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(_inven,{{"COMPANY", "DIV"}}),
    #"Multiplied Column" = Table.TransformColumns(#"Renamed Columns", {{"QTYONH", each List.Product({_, 12}), type number}}),
    #"Divided Column" = Table.TransformColumns(#"Multiplied Column", {{"SLSPRC", each _ / 12, type number}}),
    #"Divided Column1" = Table.TransformColumns(#"Divided Column", {{"FIRSTCST", each _ / 12, type number}}),
    #"Divided Column2" = Table.TransformColumns(#"Divided Column1", {{"LNDCST", each _ / 12, type number}}),
    #"Multiplied Column1" = Table.TransformColumns(#"Divided Column2", {{"QTYBO", each List.Product({_, 12}), type number}}),
    #"Changed Costs to Fixed Decimals" = Table.TransformColumnTypes(#"Multiplied Column1",{{"FIRSTCST", Currency.Type}, {"LNDCST", Currency.Type}}),
    #"Multiplied Column2" = Table.TransformColumns(#"Changed Costs to Fixed Decimals", {{"QTYWHSE", each List.Product({_, 12}), type number}}),
    #"Multiplied Column3" = Table.TransformColumns(#"Multiplied Column2", {{"PPBAL", each List.Product({_, 12}), type number}}),
    #"Multiplied Column4" = Table.TransformColumns(#"Multiplied Column3", {{"QTYAV", each List.Product({_, 12}), type number}}),
    #"Multiplied Column5" = Table.TransformColumns(#"Multiplied Column4", {{"PP", each List.Product({_, 12}), type number}}),
    #"Multiplied Column6" = Table.TransformColumns(#"Multiplied Column5", {{"OW", each List.Product({_, 12}), type number}}),
    #"Multiplied Column7" = Table.TransformColumns(#"Multiplied Column6", {{"RCV", each List.Product({_, 12}), type number}}),
    #"Rounded Off" = Table.TransformColumns(#"Multiplied Column7",{{"QTYBO", each Number.Round(_, 0), type number}, {"QTYWHSE", each Number.Round(_, 0), type number}, {"PPBAL", each Number.Round(_, 0), type number}, {"QTYONH", each Number.Round(_, 0), type number}, {"QTYAV", each Number.Round(_, 0), type number}, {"PP", each Number.Round(_, 0), type number}, {"OW", each Number.Round(_, 0), type number}, {"RCV", each Number.Round(_, 0), type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Rounded Off",{{"SLSPRC", Currency.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"PP", "Total Purchased"}, {"OW", "In Transit"}, {"RCV", "Total Received"}, {"QTYONH", "QtyOnhand"}, {"QTYAV", "QtyAvail"}, {"PPBAL", "QtyOnOrder"}, {"QTYBO", "QtyBO"}, {"LNDCST", "Landed Cost"}, {"FIRSTCST", "First Cost"}, {"SLSPRC", "Sell Price"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"DIV", "PRDCTG", "ITMID", "ETDDATE", "Sell Price", "PK", "WGHT", "DESC", "First Cost", "Landed Cost", "QtyOnOrder", "QtyOnhand", "QTYWHSE", "QtyBO", "QtyAvail", "In Transit", "Total Purchased", "Total Received", "MSTYLE", "PLCUSTOMER", "COPFRTLEN", "COPFRTWID", "COPFRTHT", "license", "subctg", "sellstat"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"QtyOnOrder", "QtyInWork"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns2",{"DIV", "PRDCTG", "ITMID", "ETDDATE", "Sell Price", "PK", "WGHT", "DESC", "First Cost", "Landed Cost", "QtyOnhand", "QTYWHSE", "QtyBO", "QtyAvail", "In Transit", "QtyInWork", "Total Purchased", "Total Received", "MSTYLE", "PLCUSTOMER", "COPFRTLEN", "COPFRTWID", "COPFRTHT", "license", "subctg", "sellstat"}),
    #"Multiplied Column8" = Table.TransformColumns(#"Reordered Columns1", {{"PK", each List.Product({_, 12}), type number}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Multiplied Column8",{{"COPFRTLEN", "Length"}, {"COPFRTWID", "Width"}, {"COPFRTHT", "Height"}, {"WGHT", "Weight"}, {"QTYWHSE", "QtyAllocd"}, {"QtyOnhand", "QtyOH"}, {"QtyAvail", "TotAvail"}, {"ITMID", "Style #"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"CADNO", "PGMNAME", "MULTIUPC", "REFNUM", "SPECNO", "clrcde", "bstyle", "brdcde", "flowtyp", "pkconfig", "setcount", "event", "sellstat"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns",{{"DESC", "Description"}, {"license", "LicCode"}, {"PRDCTG", "ProductCat"}, {"DIV", "Div"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns4", each ([#"Style #"] <> "85PHPG9620ST" and [#"Style #"] <> "85PHPG9621ST")),
    #"Renamed Columns5" = Table.RenameColumns(#"Filtered Rows",{{"UPCNUM", "UPC#"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns5",{"Style #"},InvtryAging,{"Style #"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"+365Qty", "181-365Qty", "91-180Qty", "0-90Qty", "+365$$", "181-365$$", "91-180$$", "0-90$$", "$ONHAND", "CURRSHIP", "OPENQTY"}, {"+365Qty", "181-365Qty", "91-180Qty", "0-90Qty", "+365$$", "181-365$$", "91-180$$", "0-90$$", "$ONHAND", "CURRSHIP", "OPENQTY"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded NewColumn",{"ETDDATE", "subctg", "PLCUSTOMER", "Length", "Width", "Height", "LicCode", "Weight", "UOM", "SEA"}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Removed Columns1",{"Div", "ProductCat", "Style #", "MSTYLE", "Sell Price", "PK", "Description", "First Cost", "Landed Cost", "QtyOH", "QtyAllocd", "QtyBO", "TotAvail", "In Transit", "QtyInWork", "Total Purchased", "Total Received", "UPC#", "Last_shipped", "+365Qty", "181-365Qty", "91-180Qty", "0-90Qty", "+365$$", "181-365$$", "91-180$$", "0-90$$", "$ONHAND", "CURRSHIP", "OPENQTY"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"ProductCat", "UPC#"}),
    #"Reordered Columns3" = Table.ReorderColumns(#"Removed Columns2",{"Div", "Style #", "MSTYLE", "Description", "PK", "Sell Price", "First Cost", "Landed Cost", "QtyOH", "QtyAllocd", "QtyBO", "TotAvail", "In Transit", "QtyInWork", "Total Purchased", "Total Received", "Last_shipped", "+365Qty", "181-365Qty", "91-180Qty", "0-90Qty", "+365$$", "181-365$$", "91-180$$", "0-90$$", "$ONHAND", "CURRSHIP", "OPENQTY"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns3",{{"CURRSHIP", Int64.Type}, {"OPENQTY", Int64.Type}, {"0-90Qty", Int64.Type}, {"91-180Qty", Int64.Type}, {"181-365Qty", Int64.Type}, {"+365Qty", Int64.Type}, {"Total Received", Int64.Type}, {"Total Purchased", Int64.Type}, {"QtyInWork", Int64.Type}, {"In Transit", Int64.Type}, {"TotAvail", Int64.Type}, {"QtyBO", Int64.Type}, {"QtyAllocd", Int64.Type}, {"QtyOH", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Active?", each if [TotAvail] >= 10 then "YES" else "NO" ),
    #"Renamed Columns6" = Table.RenameColumns(#"Added Conditional Column",{{"MSTYLE", "Mstyle"}, {"OPENQTY", "OpenQty"}, {"$ONHAND", "$OnHand"}, {"TotAvail", "Total Pipeline"}})
in
    #"Renamed Columns6"

[Greg_Deckler]

Well, a Left Outer join, to my understanding should actually return all of the records in the left table, 11,597 theoretically. 

But @ImkeF is far better at M than I so perhaps she can shed some light on the situation.

[ImkeF]

Cannot spot anything really suspicious here. You have many redundant "Reordered Columns"-steps in here that you could remove (despite the last). But I would be surprised if they cause trouble.

Just to check: When you say the load returns only 1000 rows, do you really mean the load to the data model or just the preview in the query editor?