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 ?

 

[JEC]

it sounds like you need to merge the new export with the already existing dataset in your Excel table.

 

[KennethVH]

it sounds like you need to merge the new export with the already existing dataset in your Excel table.

That is correct, but how can I do this ?

 

[JEC]

It is not straightforward. This is the complete code (in one query) but you need to do this in two steps.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TableWithoutInput = Table.AddColumn(Source, "Remark", each null),
    TableWithInput =  Excel.CurrentWorkbook(){[Name="Table1_1"]}[Content],
    Merge = Table.NestedJoin(TableWithoutInput, {"a"}, TableWithInput, {"a"}, "Added Custom", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Added Custom", {"Remark"}, {"Remark.1"}),
    DelCol = Table.RemoveColumns(Expand,{"Remark"}),
    Result = Table.RenameColumns(DelCol,{{"Remark.1", "Remark"}})
in
 
   Result

Book1
	A	B	C	D	E	F
1	a	b		a	b	Remark
2	1	t		1	t
3	2	y		2	y	test
4	3	u		3	u
Sheet1

Take these tables as example to practice.
First run this part en load it.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TableWithoutInput = Table.AddColumn(Source, "Remark", each null)
in
   TableWithoutInput

Then open the query again and replace the code with the complete code I posted above (there has to be remarks column before you can refer to it).
Note that the query table is called "Table1_1" in my case. It is defined in this line.

TableWithInput =  Excel.CurrentWorkbook(){[Name="Table1_1"]}[Content]

 

[KennethVH]

It is not straightforward. This is the complete code (in one query) but you need to do this in two steps.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TableWithoutInput = Table.AddColumn(Source, "Remark", each null),
    TableWithInput =  Excel.CurrentWorkbook(){[Name="Table1_1"]}[Content],
    Merge = Table.NestedJoin(TableWithoutInput, {"a"}, TableWithInput, {"a"}, "Added Custom", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Added Custom", {"Remark"}, {"Remark.1"}),
    DelCol = Table.RemoveColumns(Expand,{"Remark"}),
    Result = Table.RenameColumns(DelCol,{{"Remark.1", "Remark"}})
in
 
   Result

Book1
	A	B	C	D	E	F
1	a	b		a	b	Remark
2	1	t		1	t
3	2	y		2	y	test
4	3	u		3	u
Sheet1

Take these tables as example to practice.
First run this part en load it.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TableWithoutInput = Table.AddColumn(Source, "Remark", each null)
in
   TableWithoutInput

Then open the query again and replace the code with the complete code I posted above (there has to be remarks column before you can refer to it).
Note that the query table is called "Table1_1" in my case. It is defined in this line.

TableWithInput =  Excel.CurrentWorkbook(){[Name="Table1_1"]}[Content]

Sorry followed your steps, but I can't seem to get it working properly.

Before the merge the TablewithInput has a remark "test"

After the expand I schould see the "test" remark,but instead I see Null

So in the Result the Remarks are all empty.

 

[JEC]

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

 

[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

I Got it to work when the query is on a second tab (default), but when I try to do it like you on D1 it doesn't work. Perhaps I schould checked some wrong radiobutton on the popup screen ?

 

[KennethVH]

I Got it to work when the query is on a second tab (default), but when I try to do it like you on D1 it doesn't work. Perhaps I schould checked some wrong radiobutton on the popup screen ?
View attachment 104067

Ok, I think i'm not to check the checkbox saying Append the data to the model?
Unchecking it does the trick

Now trying to fit it in my much larger table with grouped fields
Thanks a lot for your help

 

[JEC]

You're welcome! Cheers

 

[smozgur]

Thanks a lot for your help

The actual post that answered the question has been marked as the solution. Please do the same in the future to help future readers.