Skip to main content

Notifications

Power Automate - General Discussion
Answered

Excel to match data from SQL

(0) ShareShare
ReportReport
Posted on by 11

I have a problem with maintaining a SQL table from an Excel file. Found a similar request here: Excel for-each to match data with SQL and tried to follow the solution.

 

My problem is that I get an error in the select query.

Message is: "Term 'Key_Field eq 'xxxxxx'' is not valid in a $select or $expand expression."

The Key_Field is the name of my field in the SQL table, that holds the same info as the field Key coming from the Excel file.

SelectQuery.png

Then some compose fields, before this:

ConditionEmpty.png

The lefthand side of the condition: empty(body('Get_rows_(V2)')?['value'])

But I'm not even getting to that stage.

 

So, instead I tried to remove the Select Query from 'Get Rows (V2)', and add the key fields in the condition part, like this:

ConditionFields.png

I have more fields to add to the condition, but picked only one, for the test of it. It results in False, even though both fields are text fields. So to secure that the field from Excel actually is a text field, I tried to do a compose action to create a string, and use the output for comparison, but the result is the same: False.

 

How do I get the comparison to work, so I can insert new rows and update existing ones?

  • ATG_DLF Profile Picture
    ATG_DLF 11 on at
    Re: Excel to match data from SQL

    @Anonymous; you're right, moving the query to the filter instead of the select, got it to work.

    But I still think it is a little more complicated than expected.

  • Verified answer
    Re: Excel to match data from SQL

    If I understand correctly you are trying to retrieve rows from SQL based on a condition.

    It Looks as if the 'select query' box may be misnamed in that this says what fields to return.

    The correct way appears below (it worked for me!) is to specify the odata query in the filter query box.

     

    Stan

     

     

    2020-03-11_13-16-00.jpg

  • ATG_DLF Profile Picture
    ATG_DLF 11 on at
    Re: Excel to match data from SQL

    Seems I'm not the only one, that are not able to get this to work.

     

    I have tried a lot of other approaches, and I get stuck when I get to the comparison part of the flow. No matter how I set up the 'Condition'-step, it always result in false.

    I have tried adding a filter to the query, and a top 1 count, then using this function in the left hand side of the condition: 

    empty(body('Get_items')?['value'])

    But it still results in false, even when the target list is empty.

     

    I'm having a hard time seeing the value of this product, and at no point see it as a 'no code'-product. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,475

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,767

Leaderboard