Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RalphHoffmann
Regular Visitor

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?

1 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

 

View solution in original post

4 REPLIES 4
Vera_33
Resident Rockstar
Resident Rockstar

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

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.

Hi @RalphHoffmann 

 

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

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

I have now solved the problem with VBA.

Best regards
Ralph

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.