Add rows with data from another table??

[unknown917]

I have a table with 3 columns that signify the unique ID, the day in the month the ID is serviced and the sequence in service on the given day.  I have another table with 3 columns that signify the unique ID, the plant the ID gets fulfilled from and the number that is assigned to the grouping of IDs.

I am in need of assigning the plant as the 1st stop or origination location ahead of every Day Seq 1 and the plant as the last stop or destination location in order to correctly calculate distance per day.  Is it possible to add rows and change the sequencing to get the intended result below?

ID	Day #	Day Seq
B	2	1
C	2	2
D	2	3
E	2	4
F	2	5
G	2	6
H	2	7
I	2	8
J	2	9

ID	Number	Plant
B	12	456
C	12	456
D	12	456
E	12	456
F	12	456
G	12	456
H	12	456
I	12	456
J	12	456

Intended result:

ID	Day #	Day Seq
456	2	1
B	2	2
C	2	3
D	2	4
E	2	5
F	2	6
G	2	7
H	2	8
I	2	9
J	2	10
456	2	11

[ronrsnfld]

Given your two original tables, which I have termed "TableA" and "TableB", the following code will transform it into your desired result in a performative way.

It assumes that your "real" data might include multiple Days and that you want the output to be grouped by Days.
If you have multiple plants, you might need to add an Index column to TableB as the Table.Group function does not guarantee maintaining the original row order (according to Microsoft documentation).

If your real data has more variations than I have accounted for, the code may need further editing. Feel free to provide a more representative sample if that is the case.

Code is annotated but ask if you have any questions about the algorithm.

let

//Join the two tables based on ID column
    Source = Table.NestedJoin(TableA, {"ID"}, TableB, {"ID"}, "TableB", JoinKind.LeftOuter),

//Expand the required Number and Plant Columns from the Join
    #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Number", "Plant"}),

//Group by Day #
    #"Day Groups" = Table.Group(#"Expanded TableB","Day #",{
        {"Routing", (t)=>
            [a=Table.Sort(t,{"Day Seq", Order.Ascending}),      //ensure proper Day Seq. Table.Group does not guarantee original order
             b=Table.RemoveColumns(a,{"Day Seq"}),              //Remove Existing Day Seq column
             c={Table.First(b)} & Table.ToRecords(b) & {Table.Last(b)},     //duplicate first and last rows
             d=Table.FromRecords(c),                            //convert to table
             e=Table.AddIndexColumn(d,"Day Seq",1,1,Int64.Type), //Add back a Day Seq column
             f=Table.ReplaceValue(                              //Replace First and Last ID's in new table with Plant
                e,
                each [Plant],
                each [Day Seq],
                (x,y,z)=> if z=List.Max(e[Day Seq]) or z=List.Min(e[Day Seq]) then y else x,
                {"ID"}
             )
            
            ][f], type table[ID=any, #"Day #"=Int64.Type, Day Seq=Int64.Type]} //set the data types
    }),

//Expand the required resultant table columns
    #"Removed Columns" = Table.RemoveColumns(#"Day Groups",{"Day #"}),
    #"Expanded Routing" = Table.ExpandTableColumn(#"Removed Columns", "Routing", {"ID", "Day #", "Day Seq"})
in
    #"Expanded Routing"

[Poojara_D12]

Hi @oussamahaimoud 

To inject the plant as both the start (sequence 1) and end (sequence 11) location for your route planning without breaking your existing data structure, you can do this completely inside Power Query using a structured reference and append technique. First, merge your two tables based on the ID column so that every row has its corresponding Plant and Grouping Number assigned. Next, create two referenced copies of this merged table to build your artificial start and end rows: for the start rows, filter your data where Day Seq equals 1, create a custom column named New_ID using your [Plant] field, a custom column named New_DaySeq set to 1, and offset your original customer sequences by adding 1 to them so they shift downward. For the end rows, group your data by Day # to find the maximum sequence of the day, filter for those rows, set the New_ID to your [Plant] field, and set the New_DaySeq to your maximum sequence plus 2. Finally, remove the old ID and sequence columns across your sub-tables, rename your new custom columns to match the original schema, combine all three tables together using the Append Queries as New feature, and sort the final appended output by Day # and Day Seq in ascending order to cleanly wrap every day's route from plant to plant.

[v-veshwara-msft]

Hi @unknown917 ,

Thanks for reaching out to Microsoft Fabric Community.

Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
Thank you.

[unknown917]

I have been diverted to another project.  I have not had the chance to come back to this yet.  Thank you for following up

[v-veshwara-msft]

Hi @unknown917 ,

Thank you for the update.

No problem at all. Feel free to revisit this whenever you get a chance, and we’ll be happy to assist further if needed.

Thank you.

[v-veshwara-msft]

Hi @unknown917 ,

Just checking in to see if you had a chance to revisit this and need any further assistance. Please reach out if needed any assistance.

Thank you.

[SundarRaj]

Hi @unknown917 ,

From what I could understand from your query, please look over this M code and see if this what you are looking for as the ouput?

If the result is supposed to be more dynamic in terms of joins between the two tables, this could be a good starting point.

I'll attach the excel file for your reference. Here is the M code below:

let
Source = [Src],
T1 = Table.TransformColumnTypes(
Source,
{{"ID", type text}, {"Number", Int64.Type}, {"Plant", Int64.Type}},
"en-US"
),
T3 = Table.AddColumn(
Table.Group(T2, {"Day #"}, {{"A", each _}}),
"F",
each Table.TransformColumns(
Table.FirstN([A], 1),
{"ID", each T1[Plant]{List.PositionOf(T1[ID], _)}}
)
),
T4 = Table.AddColumn(
T3,
"L",
each Table.TransformColumns(
Table.TransformColumns(
Table.LastN([A], 1),
{"ID", each T1[Plant]{List.PositionOf(T1[ID], _)}}
),
{"Day Seq", each _ + 2}
)
),
C = Table.Combine(
Table.AddColumn(T4, "C", each [F] & Table.TransformColumns([A], {"Day Seq", each _ + 1}) & [L])[
C
]
)
in
C

Thanks,

[unknown917]

@SundarRaj I'm getting a circular reference error.   Any ideas on what might be causing this?

[SundarRaj]

Yes, so it seems we'll have to input the required two tables here in this code:

Source is the second table that is there in your problem statement. The table that contains ID, Number and Plant columns.
T2 is the first table which contains ID, Day #, Day Seq.

Can you try modifying the queries with these required input tables in the queries and see if that works out?
Also, check out the Power Query Editor for the file linked above.

Thanks,

[oussamahaimoud]

Hi @unknown917,

Hope you're doing well!

In Power Query, create a New Blank Query and paste this M code:

let

    // Reference your existing tables (rename to match your actual query names)

    RouteTable = Excel.CurrentWorkbook(){[Name="RouteTable"]}[Content],

    PlantTable = Excel.CurrentWorkbook(){[Name="PlantTable"]}[Content],

    // Ensure correct types

    RouteTyped = Table.TransformColumnTypes(RouteTable,{

        {"ID", type text}, 

        {"Day #", Int64.Type}, 

        {"Day Seq", Int64.Type}

    }),

    PlantTyped = Table.TransformColumnTypes(PlantTable,{

        {"ID", type text}, 

        {"Number", Int64.Type}, 

        {"Plant", type text}

    }),

    // Join Plant info onto Route rows

    Joined = Table.NestedJoin(

        RouteTyped, {"ID"}, 

        PlantTyped, {"ID"}, 

        "PlantData", JoinKind.Left

    ),

    Expanded = Table.ExpandTableColumn(

        Joined, "PlantData", {"Plant", "Number"}, {"Plant", "Number"}

    ),

    // Group by Day # (and Number/Plant if multiple groups exist)

    Grouped = Table.Group(

        Expanded, 

        {"Day #", "Number", "Plant"},

        {{"Rows", each _, type table}}

    ),

    // For each group: add START row (plant, seq=1), re-seq original rows, add END row

    Transformed = Table.TransformColumns(Grouped, {"Rows", each 

        let

            grp = _,

            dayNum = grp[Day #]{0},

            plant = grp[Plant]{0},

            rowCount = Table.RowCount(grp),

            // Build START row

            startRow = #table(

                {"ID", "Day #", "Day Seq"},

                {{plant, dayNum, 1}}

            ),

            // Re-sequence original rows starting at 2

            reSeqd = Table.AddColumn(

                Table.SelectColumns(grp, {"ID", "Day #"}),

                "Day Seq",

                each Table.RowCount(

                    Table.SelectRows(

                        Table.SelectColumns(grp, {"ID"}),

                        (r) => r[ID] <= [ID] // positional trick — see note below

                    )

                ) + 1

            ),

            // Better re-sequencing using index

            withIndex = Table.AddIndexColumn(

                Table.SelectColumns(grp, {"ID", "Day #"}),

                "Day Seq", 2, 1

            ),

            // Build END row

            endRow = #table(

                {"ID", "Day #", "Day Seq"},

                {{plant, dayNum, rowCount + 2}}

            ),

            // Stack all three parts

            Final = Table.Combine({startRow, withIndex, endRow})

        in

            Final

    }),

    // Expand grouped results back into flat table

    Result = Table.Combine(Transformed[Rows])

in

    Result

Here's the clean, production-ready version with no noise:

let

    RouteTable = YourRouteQueryName, // ← change to your query name

    PlantTable = YourPlantQueryName, // ← change to your query name

    Joined = Table.NestedJoin(RouteTable, {"ID"}, PlantTable, {"ID"}, "P", JoinKind.Left),

    Expanded = Table.ExpandTableColumn(Joined, "P", {"Plant", "Number"}),

    Grouped = Table.Group(Expanded, {"Day #", "Plant", "Number"}, {{"Rows", each _, type table}}),

    AddedRows = Table.AddColumn(Grouped, "Final", each

        let

            src=[Rows],

            day = [Day #],

            plant = [Plant],

            cnt = Table.RowCount(src),

            startRow = #table({"ID","Day #","Day Seq"}, {{plant, day, 1}}),

            middle = Table.AddIndexColumn(Table.SelectColumns(src,{"ID","Day #"}), "Day Seq", 2, 1),

            endRow = #table({"ID","Day #","Day Seq"}, {{plant, day, cnt + 2}})

        in

            Table.Combine({startRow, middle, endRow})

    ),

    Result = Table.Combine(AddedRows[Final])

in

    Result

What This Produces

ID

Day #

Day Seq

456

2

1 ← Plant (Start)

B

2

2

C

2

3

D

2

4

E

2

5

F

2

6

G

2

7

H

2

8

I

2

9

J

2

10

456

2

11 ← Plant (End)

[oussamahaimoud]

@unknown917, did you have the chance to test this solution?