cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AIUYM19
Helper V
Helper 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 III
Dual Super User III

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.

View solution in original post

Pstork1
Dual Super User III
Dual Super User III

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.

View solution in original post

6 REPLIES 6
Pstork1
Dual Super User III
Dual Super User III

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.

View solution in original post

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

 

Pstork1
Dual Super User III
Dual Super User III

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.

View solution in original post

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!

Pstork1
Dual Super User III
Dual Super User III

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (3,880)