cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jdsarmiento Frequent Visitor
Frequent Visitor

Merge queries gives "scrambbled" results

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.

 

 

 

 

4 REPLIES 4
Seward12533 New Contributor
New Contributor

Re: Merge queries gives "scrambbled" results

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

v-yulgu-msft Super Contributor
Super Contributor

Re: Merge queries gives "scrambbled" results

Hi @Jdsarmiento,

 

Please illustrate your requirement with more detailed dummy data.

How to Get Your Question Answered Quickly

 

Regards,

Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jdsarmiento Frequent Visitor
Frequent Visitor

Re: Merge queries gives "scrambbled" results

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:

Goal.pngWhere Razón social proveedor is the supplier and Tipo the Way of Shipment

 

Thanks!

Seward12533 New Contributor
New Contributor

Re: Merge queries gives "scrambbled" results

@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.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 94 members 1,436 guests
Please welcome our newest community members: