Write Values into a Excel Table

[RalphHoffmann]

Hello,
I am importing a *csv file using a query. Thereby a custom function is executed:

(FieldInput as text) =>
let
    qSource = Excel.CurrentWorkbook(){[Name="tbl_Debitors"]}[Content],
    qRow = Table.SelectRows(qSource, each ([Key]=FieldInput)),
    qContent=
            if Table.IsEmpty(qRow)=true
                then  0 
                else Record.Field(qRow{0},"Value")
in
    qContent

Now I want to change this code so that if

if Table.IsEmpty(qRow)=true

a row is inserted into the table "tbl_Debitors" and the value 'FieldInput' is written in the column [Key]. Like this

(FieldInput as text) =>
let
    qSource = Excel.CurrentWorkbook(){[Name="tbl_Debitors"]}[Content],
    qRow = Table.SelectRows(qSource, each ([Key]=FieldInput)),
    qContent=
            if Table.IsEmpty(qRow)=true
                then  Table.InsertRow("tbl_Debitors",1,[Key] = FieldInput) 
                else Record.Field(qRow{0},"Value")
in
    qContent

But this doesn't work. Does anyone have an idea how to do it right?

[Anonymous]

Hi @RalphHoffmann 

I guess you want to insert a row with Key=FieldInput and Value=0?

(FieldInput as text) =>
let
    qSource = Excel.CurrentWorkbook(){[Name="tbl_Debitors"]}[Content],
    qRow = Table.SelectRows(qSource, each ([Key]=FieldInput)),
    qContent=
            if Table.IsEmpty(qRow)=true
                then  Table.InsertRows(qSource,1,{[Key = FieldInput, Value=0]}) 
                else Record.Field(qRow{0},"Value")
in
    qContent

[RalphHoffmann]

Hello Vera,
yes. You are right. However, in the excel spreadsheet.

I have a table with the name "tbl_Debitors". Here the query should enter non-existing values in the [Key] column and the value 0 in the [Value] column.

If there is a value in the [Key] column, the customer ID should be read from the [Value] column.

[Anonymous]

Hi @RalphHoffmann 

I don't think I fully understand what you want, can you put some sample data and expected result?

[RalphHoffmann · Accepted solution]

Hello Vera,
what I wanted to do can not be done with PowerQuery.

I have now solved the problem with VBA.

Best regards
Ralph