Forum Discussion
Write Values into a Excel Table
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?
Hello Vera,
what I wanted to do can not be done with PowerQuery.I have now solved the problem with VBA.
Best regards
Ralph
4 Replies
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 qContentHello 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.
I don't think I fully understand what you want, can you put some sample data and expected result?
