cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AIUYM19
Advocate V
Advocate V

Table column names in Excel Online (business) causing flow errors.

I need to create a flow that will do scheduled analysis on an Excel workbook that is out on SharePoint. To avoid accidentally messing up the original, the first step in my flow copies the workbook to a new location, and then works with the copy. The flow then gets all rows from the DataDetails table in the workbook, and needs to conduct its analysis on this table's data.

Unfortunately, the person who maintains this workbook is using the newline character in a couple of the table column names. When my flow reaches the point where it needs to read data from these columns, it throws an error saying the column doesn't exist. For example, when trying to get data from one column in an apply to each container, I used the expression

item()['IT Owner\n(First & Last Name)']

I've also tried the expressions

item()['IT Owner
(First & Last Name)']

 and

item()['IT Owner'&char(10)&'(First & Last Name)']

These all caused the flow to throw an error at runtime. How do I reference these table columns? Is there a way to have the flow change the table column to something else, and then use the new name to reference the data? What is the best way to tackle this problem?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Pstork1
Dual Super User
Dual Super User

If you use the Data Select command on the Array returned by the Get Rows you should be able to substitute New Column names for existing column values.  That's what I would try first.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365 and Pwr EduCon in Chicago
EduCon Chicago 2022

View solution in original post

Based on your screen shot isn't your mapping backwards?  The new Column name should go in the Key Column on the Left and the column value on the right. Your screenshot shows it the other way around.

image.png



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365 and Pwr EduCon in Chicago
EduCon Chicago 2022

View solution in original post

6 REPLIES 6
Pstork1
Dual Super User
Dual Super User

If you use the Data Select command on the Array returned by the Get Rows you should be able to substitute New Column names for existing column values.  That's what I would try first.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365 and Pwr EduCon in Chicago
EduCon Chicago 2022

DamoBird365
Super User
Super User

Hi @AIUYM19 

 

If you try "list rows present in a table"

 

DamoBird365_0-1614621747745.png

and then click on output, you will be able to see your row names like so:

 

DamoBird365_1-1614621882731.png

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

Hi, @Pstork1.

I completely forgot about the Select action. Thanks for the reminder. It's giving me a strange error, though.

Screenshot 2021-03-01 133058.png

 I'm not sure why it's having a problem with the "Customer Service" column, or why it's trying to rename it as an empty string. That entry in my select action is...

Screenshot 2021-03-01 133351.png

 

Based on your screen shot isn't your mapping backwards?  The new Column name should go in the Key Column on the Left and the column value on the right. Your screenshot shows it the other way around.

image.png



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365 and Pwr EduCon in Chicago
EduCon Chicago 2022

Oh my goodness, you're absolutely right. 🤣 I have no idea what I was thinking there. That's what happens when you are too focused to really pay attention to what you're doing, I guess. I need to slow down!

Thanks!

As a developer, sometimes your eyes see what you expect.  I've done it myself where someone else sees an obvious flaw in my code.  A second set of eyes is always useful. That's why screenshots are so important.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365 and Pwr EduCon in Chicago
EduCon Chicago 2022

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (2,528)