Merge queries gives "scrambbled" results

[Jdsarmiento]

Hi Everyone,

Im a very rookie user and this is my first post ever so please bear with my lack of experience (and english since this isnt my main language).

My dataset contains 2 databases, one represents Orders and the other represent deliveries for those orders (partial deliveries most of the time).

Basically what i need to do is calculate the average time it takes to deliver 90% or more of the order and how many deliveries it took.

For this i've been lurking the forums (used the following one about Nested Index to do the Delivery # ) but im having some issues here.

https://www.youtube.com/watch?v=-3KFZaYImEY

The issues is that the way i did it i had to create a second Deliveries Table then create a CONCATENATE column and delete duplicates there (this because i can have different items on a delivery/order and i just need an index per delivery, not per item).

Then i'd Inner Merge the original Delivery table and this new Delivery with unique values and expand the index.

This works fine on the alternate table for assigning the Index but when i merge to "call" those index on the original table i get scrambbled results (on some lines only):

https://imgur.com/a/oiKjwwQ

Alternate query for assigning the index:

let
    Source = Folder.Files("C:\Users\jdsarmiento\Desktop\Power BI\Informe tiempos\Ordenes"),
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Ordenes", each #"Transform File from Ordenes"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Ordenes"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Ordenes", Table.ColumnNames(#"Transform File from Ordenes"(#"Sample File (2)"))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Bodega", type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Fecha orden", type date}, {"Nro orden", type text}, {"Nro solicitud", type text}, {"Precio unit.", type number}, {"Cant. ordenada", Int64.Type}, {"Fecha entrega", type date}, {"Cant. pendiente", Int64.Type}, {"Razón social proveedor", type text}, {"Referencia", type text}, {"Desc. item", type text}, {"Fecha cumplido", type datetime}, {"Notas documento", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"Referencia", "Nro orden", "Nro solicitud"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if [Bodega] = "10005" then "4W" else if [Bodega] = "11005" then "2W" else null),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Custom", "Division"}}),
    #"Split Column by Position" = Table.SplitColumn(#"Renamed Columns", "Nro solicitud", Splitter.SplitTextByPositions({0, 4}, false), {"Nro solicitud.1", "Nro solicitud.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position",{{"Nro solicitud.1", type text}, {"Nro solicitud.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Nro solicitud.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Nro solicitud.2", "Nro solicitud"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns1",{"Nro solicitud", "Referencia"},Solicitudes,{"Nro solic.", "Referencia"},"Solicitudes",JoinKind.LeftOuter),
    #"Expanded Solicitudes" = Table.ExpandTableColumn(#"Merged Queries", "Solicitudes", {"Fecha solic."}, {"Fecha solic."}),
    #"Sorted Rows" = Table.Sort(#"Expanded Solicitudes",{{"Division", Order.Ascending}, {"Nro solicitud", Order.Ascending}, {"Fecha orden", Order.Ascending}, {"Nro orden", Order.Ascending}}),
    #"Removed Other Columns2" = Table.SelectColumns(#"Sorted Rows",{"Fecha orden", "Nro orden", "Nro solicitud"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns2", "Concatenado", each [Nro orden]&[Nro solicitud]),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Concatenado"}),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Nro solicitud"}, {{"Count", each Table.AddIndexColumn(_, "Index",1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Fecha orden", "Nro orden", "Concatenado", "Index"}, {"Fecha orden", "Nro orden", "Concatenado", "Index"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Expanded Count",{{"Fecha orden", type date}, {"Nro orden", type text}, {"Concatenado", type text}})
in
    #"Changed Type4"

Deliveries Query:

let
    Source = Folder.Files("C:\Users\jdsarmiento\Desktop\Power BI\Informe tiempos\Ordenes"),
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Ordenes", each #"Transform File from Ordenes"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Ordenes"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Ordenes", Table.ColumnNames(#"Transform File from Ordenes"(#"Sample File (2)"))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Bodega", type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Fecha orden", type date}, {"Nro orden", type text}, {"Nro solicitud", type text}, {"Precio unit.", type number}, {"Cant. ordenada", Int64.Type}, {"Fecha entrega", type date}, {"Cant. pendiente", Int64.Type}, {"Razón social proveedor", type text}, {"Referencia", type text}, {"Desc. item", type text}, {"Fecha cumplido", type datetime}, {"Notas documento", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"Referencia", "Nro orden", "Nro solicitud"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if [Bodega] = "10005" then "4W" else if [Bodega] = "11005" then "2W" else null),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Custom", "Division"}}),
    #"Split Column by Position" = Table.SplitColumn(#"Renamed Columns", "Nro solicitud", Splitter.SplitTextByPositions({0, 4}, false), {"Nro solicitud.1", "Nro solicitud.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position",{{"Nro solicitud.1", type text}, {"Nro solicitud.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Nro solicitud.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Nro solicitud.2", "Nro solicitud"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns1",{"Nro solicitud", "Referencia"},Solicitudes,{"Nro solic.", "Referencia"},"Solicitudes",JoinKind.Inner),
    #"Expanded Solicitudes" = Table.ExpandTableColumn(#"Merged Queries", "Solicitudes", {"Fecha solic."}, {"Fecha solic."}),
    #"Sorted Rows" = Table.Sort(#"Expanded Solicitudes",{{"Division", Order.Ascending}, {"Nro solicitud", Order.Ascending}, {"Fecha orden", Order.Ascending}, {"Nro orden", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Concatenado", each [Nro orden]&[Nro solicitud]),
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom",{"Concatenado"},#"Maestro ordenes",{"Concatenado"},"Maestro ordenes",JoinKind.Inner),
    #"Expanded Maestro ordenes" = Table.ExpandTableColumn(#"Merged Queries1", "Maestro ordenes", {"Index"}, {"Index"})
in
    #"Expanded Maestro ordenes"

Hope i was clear enough but please dont hesitate to ask anything if i wasnt.

Thanks!

David.

[v-yulgu-msft]

Hi @Jdsarmiento,

Please illustrate your requirement with more detailed dummy data.

How to Get Your Question Answered Quickly

Regards,

Yuliana Gu

[Jdsarmiento]

Hi @v-yulgu-msft and @Seward12533

Thank you for taking the time to look at my post.

Following Yuliana's advice i am sharing my data on the following link:

https://www.dropbox.com/sh/t0g37v3thx5sek6/AACDrDAmUr9hqSY37ZdDmdIsa?dl=0

The data is in Spanish but basically Nro Solicitud= Order number and Nro Orden = Shipping code.

As you will be able to see on the Query "Maestro ordenes" the index is working fine (indexing each different shipping from an order) but the merge at "Ordenes" to retrieve this index is not working for some reason.

What i want to do is after indexing the Shippings create a cummulative total for each order (also working in this measure) and then calculate # fo days it took to get over 90%.

This is the approach i've taken but the final goal is to calculate in average how long is taking to achieve 90% of the orders and in how many deliveries so if you find a different way to get this result it will be very helpful. (How many deliveries is not a must have but would be great if possible)

The final information i'd like to show is something like this:

Where Razón social proveedor is the supplier and Tipo the Way of Shipment

Thanks!

[Seward12533]

@JdsarmientoUnfortunately I cannot check the PowerQuery M as I can't link to your data.  I believe the reason your index is showing what seems like incorrect behaviour is your creating this index based on the Nro Orden = Shipping code and that is not unique.  I would recommend adding a new column appending teh Order Number and Shipping Code and calculating your index based on that.

[Seward12533]

Can you share what your data tables look like oir ideally provide link to workbook with representative data.  As an alternative to doing in M you may try a caclualted column similar to approch I used in this solution for a different problem to calcualte the Delivery Number. 

There is probably a slicker solution using earlier but my apprach would be to add an index column to your data. Crease a Lookup Table for index number (you can use GENERATESERIES and specify the range to be from 1 to MAX Index incrementing by 1 to create the table dynamically form DAX.  Link it to your table an then do a Calculated Column that counts the number of Rows with the same Order Number by using CALCULATE(COUNTROWS(VALUES(table[ordernumber]),ALL(Index),FILTER(ALL(Index[Index]),Index[Index]<=[Index])  this will give you a shipment # for each of the dlivery. you can then write measures to calcualte the %Complete and # of deliveries. 

To do the part of calcualting how many orders it took to hit 90% might be a little tricker.  Would probably requrire doing this on a row level with a calcualted column as well but calcualting the actual % of Total Order Complete on a pre row and then finding the MIN Delivery # of the order lines that had over 90% in the value in the % of Total Order column. 

You can see some of these concepts applied in this soluion -