Powerquery - Algorithm required for this task

Question

Long time lurker, first time poster. Apologies in advance if my question appears a bit noobish.

I am new to powerquery/powerbi and trying to develop a report to solve a small logistics issue we are facing.

I have a list of cables that are given to our technicians to use of different pre-terminated lengths which they will use for their application. Ideally the closest length cable to the length of the route should be utilized, however that is not always the case. I want to match the cables issued to the routes (Column "Actual Cables Utilised"). Example below:

Cables issued:

Cables isuued

Breakdown:

Breakdown

Highlighted in green are the accepted substitutions. Red is the wrong substitution (There isn't a cable issued that is longer than the route).

I cannot find a way to run a loop in powerquery that would populate the breakdown table based on the issued table. Please note that unfortunately, my company does not allow python or r-script on our computers, so I am limited to basic power query.

Any assistance would be greatly appreciated!

user_2872922 · Answer

This might work

Group the Breakdown table by technician
For each technician, generate a list of Issued cables with an entry for each cable (i.e. if there are 2 - 40m cables, 40 will appear twice in the list.
- The Issued cables will be assigned sequentially in the order presented by the Breakdown sheet.
Use a recursive function that, as it generates the Utilized cable list, removes that entry from the Issued cables.

Main Code

let

//Read in the Issued and Breakdown tables
//Change the Source lines to reflect your own data sources for these tables
    Source1 = Excel.CurrentWorkbook(){[Name="Issued"]}[Content],
    Issued = Table.TransformColumnTypes(Source1, List.Transform(Table.ColumnNames(Source1), each {_, Int64.Type})),

    Source = Excel.CurrentWorkbook(){[Name="Breakdown"]}[Content],
    Breakdown = Table.TransformColumnTypes(Source,{
        {"Technician", type text}, {"Route Length (m)", Int64.Type}, {"Ideal Cable Length (m)", Int64.Type}}),

//Group by Technician
// Generate a list of cables where there is one entry for every issued cable
// Then call the custom (recursive) function to generate an appropriate list for each tech
    #"Grouped Rows" = Table.Group(Breakdown, {"Technician"}, {
        {"Cables", (t)=> 
            let
             cables = List.Combine(
                List.Generate(
                    ()=> [c=List.Repeat({Issued[Cable Lengths]{0}}, Table.Column(Issued, t[Technician]{0}){0}), idx=0],
                    each [idx] < Table.RowCount(Issued),
                    each [c=List.Repeat({Issued[Cable Lengths]{[idx]+1}}, Table.Column(Issued, t[Technician]{0}){[idx]+1}), idx = [idx]+1],
                    each [c])),
             x = fnBreakdown(t, cables)
            in
                x, type list}}),
    #"Expanded Cables" = Table.ExpandListColumn(#"Grouped Rows", "Cables"),

//add the utilized cable column back to the Breakdown table
    #"Breakdown Table" = Table.FromColumns(
        Table.ToColumns(Breakdown) & {#"Expanded Cables"[Cables]},
        type table[Technician=text, #"Route Length (m)"=Int64.Type, 
            #"Ideal Cable Length (m)"=Int64.Type,Actual Cable Utilized=Int64.Type]
    )
in
    #"Breakdown Table"

Custom Function
rename fnBreakdown

//Rename this query "fnBreakdown"

//tech = the subtable all with the same technician
//cables = a list of cables with a single entry for each size
//   generated by List.Repeat in the original call from the main code
//cableList = a list of the utilized cable
//Index is the index into the Tech table

(tech as table, cables as list, optional cableList as list, optional Index as number)=>

let

//set intial values for optional arguments
    idx = if Index = null then 0 else Index + 1,
    cableL = if cableList = null then {} else cableList,

//determine cable matching for "idx"th entry
//if there is no match then write a null
    #"New Cab" = List.Select(cables, each _ >= tech[#"Route Length (m)"]{idx}),
    #"New Cable" = if #"New Cab" = {} then null else #"New Cab"{0},

//Remove the used cable from the list of "cables"
    #"New Cables" = if List.PositionOf(cables,#"New Cable",Occurrence.First) = -1 
        then cables 
        else List.RemoveRange(cables,List.PositionOf(cables,#"New Cable",Occurrence.First),1),

//new cable list for this tech
    #"New Cable List" = cableL & {#"New Cable"},

//if we haven't checked all the routes then recursively call the function with the updated arguments
    result = if idx < Table.RowCount(tech)-1 
                then  fnBreakdown(tech,#"New Cables", #"New Cable List", idx)
                else #"New Cable List"
in 
    result

Results from your data above

Powerquery - Algorithm required for this task

1 Answers