Can you add data to a Power Query ?

[KennethVH]

From our accounting program I can do an export of order data. (order-ID, amount, invoice-ID) If the order is not yet invoiced then the invoi-ID is still blank.
I use power Query to clean upand sort the data. So far so good.

Now I want to add an extra column in excelTable/PowerQuery to INPUT the date when the order was approved (this field is not in the accounting program).

I have done this in the excelTable (after save & update) , he created an extra column at the end, to put in the aproval date
But when I get an updated export from the accounting (with old and new datalines/info), my approval date stays on the same row(it doesn't seem to be connected to the order-id ).

That way I think order 3 was approved while it was actualy another order .
Summary : Can you add data to a powerquery data set?
Maybe I'm doing it wrong or using the wrong tool, can anybody help ?

 

[KennethVH]

Have you exactly followed the steps? It really should work.

The blue table is the base table. The green table is the query table.

Run the first part on the blue table and load it. Add a testcomment and go to edit the query again. Then paste the complete code

JEC, almost there but now I want to keep not 1 but 3 columfields "Verzonden", "Goedkeuring" and "Goedkeuring_AXI" from the TableWithInput.

But I can't seem to get the syntax right.

Can you help me one more time ? The error message is on the EXPAND= line where he gives :
Expression.Error: The field 'Verzonden' already exists in the record

let
    Bron = Folder.Files("H:\Downloads"),
    #"Rijen gefilterd" = Table.SelectRows(Bron, each ([Extension] = ".xlsx")),
    #"Rijen gefilterd1" = Table.SelectRows(#"Rijen gefilterd", each Text.StartsWith([Name], "purchase_order_lines")),
    #"Rijen gesorteerd" = Table.Sort(#"Rijen gefilterd1",{{"Date created", Order.Descending}}),
    #"Eerste rijen behouden" = Table.FirstN(#"Rijen gesorteerd",1),
    #"FileLocation" = #"Eerste rijen behouden"{0}[Content],
    #"Geïmporteerde Excel-werkmap" = Excel.Workbook(#"FileLocation"),
    sheet1_Sheet = #"Geïmporteerde Excel-werkmap"{[Item="sheet1",Kind="Sheet"]}[Data],
    #"Headers met verhoogd niveau" = Table.PromoteHeaders(sheet1_Sheet, [PromoteAllScalars=true]),
    #"Type gewijzigd" = Table.TransformColumnTypes(#"Headers met verhoogd niveau",{{"Dim 1-Project", Int64.Type}, {"Ordernummer", Int64.Type}, {"Orderdatum", type date}, {"Inkoopmedewerker", type text}, {"Extern nummer", type text}, {"Leverancier", type text}, {"Grootboek", Int64.Type}, {"Dim 4-ESR Code", type text}, {"Maatstafbedrag", type text}, {"Totaal", type number}, {"Documentnummer", type text}, {"Beoordelingsstatus inkooporder", type text}, {"Verzendstatus", type text}, {"Boekingstatus", type text}, {"Delgingstatus", type text}, {"Column16", Int64.Type}, {"Column17", type any}, {"Column18", Int64.Type}, {"Column19", type any}, {"Column20", Int64.Type}, {"Column21", type any}, {"Column22", Int64.Type}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type number}, {"Column37", type number}}),
    #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Type gewijzigd",{{"Beoordelingsstatus inkooporder", "Beoordelingsstatus"}}),
    #"Kolommen verwijderd" = Table.RemoveColumns(#"Namen van kolommen gewijzigd",{"Documentnummer", "Boekingstatus", "Delgingstatus", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37"}),
    #"Rijen gegroepeerd" = Table.Group(#"Kolommen verwijderd", {"Dim 1-Project","Orderdatum","Ordernummer","Inkoopmedewerker","Extern nummer","Leverancier","Grootboek","Dim 4-ESR Code","Beoordelingsstatus","Verzendstatus"}, {{"Raming", each List.Sum([Totaal]), type nullable number}}),
    #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Rijen gegroepeerd",{"Dim 1-Project", "Orderdatum", "Ordernummer", "Inkoopmedewerker", "Extern nummer", "Leverancier", "Grootboek", "Dim 4-ESR Code", "Raming", "Beoordelingsstatus", "Verzendstatus"}),

// Stap : Voeg de kolommen "Verzonden", "Goedkeuring" en "Goedkeuring_AXI" toe
    AddColumns = Table.AddColumn(#"Volgorde van kolommen gewijzigd", "Verzonden", each null, type date),
    AddColumns2 = Table.AddColumn(AddColumns, "Goedkeuring", each null, type date),
    AddColumns3 = Table.AddColumn(AddColumns2, "Goedkeuring_AXI", each null, type date),
    TableWithoutInput = AddColumns3,
// einde ophalen nieuwe update


    TableWithInput =  Excel.CurrentWorkbook(){[Name="Downloads"]}[Content],
    Merge = Table.NestedJoin(TableWithoutInput, {"Ordernummer"}, TableWithInput, {"Ordernummer"}, "Added Custom", JoinKind.LeftOuter),
   
 
    Expand = Table.ExpandTableColumn(Merge, "Added Custom", {"Verzonden", "Goedkeuring", "Goedkeuring_AXI"}, {"Verzonden", "Goedkeuring", "Goedkeuring_AXI"}),

    // Verwijder de oorspronkelijke geneste kolom
    DelCol = Table.RemoveColumns(Expand, {"Added Custom"}),

    // Hernoem de uitgevouwen kolommen
    Result = Table.RenameColumns(DelCol, {{"Verzonden.1", "Verzonden"}, {"Goedkeuring.1", "Goedkeuring"}, {"Goedkeuring_AXI.1", "Goedkeuring_AXI"}})



in
 
   Result

 

[JEC]

Give the columns different names in the expand line so they differ from the column names which are added earlier. The error is already telling you the problem

Ps: je bent op de goede weg