Power Query not able to identify the position of new rows

[acerNZ]

Hi @edhans and @Jimmy801 

This is a follow up question on selecting any row as header. 

https://community.powerbi.com/t5/Power-Query/Selecting-a-row-as-header/m-p/1483575#M46285 

Hey @edhans  and @Jimmy801  When I wanted to take both the solutions to the my actual work file, I get issues as i modified relevant fields so I added another row in the excel sheet 

@Jimmy801  

 -- Changed 3 to 4 TableSkip = Table.Skip(Sheet1_Sheet, 4),
Changed to How.. #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"How", type any},

but I get error saying "table how" is not found, but when I revert back to tableskip to 3 and promoted Headers to "East" works like charm

I get same issues with @edhans , When I use " How" as idenfier of my new row and coloumn 1, 

So I tied = 

= List.PositionOf(Sheet1_Sheet[Column1], "How") 

I get -1 not 5 or 6 .. hmm I created a few more rows and tired, same -1, 

Any idea why power BI is not recognizing the new rows added, I can see them when I loaded it. Note: Yes each time I am loading excel afresh and able to see my data in the power BI, just that in the formula, I don't get it right

Any help would be appreciated

[Jimmy801 · Accepted solution]

Hello @acerNZ 

if the only problem is now to identify your first row dynamically, as @edhans was suggesting, his solution works fine. The reason why you get -1 is that in your data there is no cell that contains "How", but only "How ". So change this, and the solution will work. However, afterwards you have to launch Promote headers from the GUI in order Power Query does a) promote headers and b) trying to identify the type of every column. But be aware that if you data may change (new column or deleted column) you have to delete this step, because in this step all column headers a hard-coded.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[acerNZ]

Sorry @PhilipTreacy @Jimmy801  I did stuff like retype, copy same field and thought it was not issue. I tied both the solutions of @Jimmy801 and @edhans .. works like charm

[PhilipTreacy · Accepted solution]

Hi @acerNZ 

The transformations are specific to an expected sheet layout so random data will break them.

In the promotedheader.xlsx file you only have 3 rows at the top of the sheet that need to be removed before promoting headers.

= Table.Skip(Sheet1_Sheet,3)

But in the promotedheader_test2.xlsx file you have 4 rows that need to be removed befoe promoting headers.  

= Table.Skip(Sheet1_Sheet,4)

If your source data structure isn't consistent then these transformations will break.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Jimmy801 · Accepted solution]

Hello @acerNZ 

if the only problem is now to identify your first row dynamically, as @edhans was suggesting, his solution works fine. The reason why you get -1 is that in your data there is no cell that contains "How", but only "How ". So change this, and the solution will work. However, afterwards you have to launch Promote headers from the GUI in order Power Query does a) promote headers and b) trying to identify the type of every column. But be aware that if you data may change (new column or deleted column) you have to delete this step, because in this step all column headers a hard-coded.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[acerNZ]

I thought @edhans solution is based on column identifier and @Jimmy801 is table skip number and column identifier. I am dumb anyways. So I will wait

[acerNZ]

Thank you @PhilipTreacy  

Oh my data can be random and 100s of coloumns 🙂 

here is the screenshot 

Please can you check if you can verify the file here: https://github.com/acerNZ/PowerBI.git  Thanks a lot

[PhilipTreacy]

Hi @acerNZ 

Both solutions by @edhans and @Jimmy801 rely on yor data being in a set format.  That is, there are 3 rows at the top of your Excel file that will discarded leaving the 4th row to be promoted as Headers.

In addition, it is expected that the word in Column 1 is East:

You say you've added another row in the sheet- where?  And that you've modified fields- how so?  A screenshot would be useful.

When you try this

= List.PositionOf(Sheet1_Sheet[Column1], "How") 

and get -1 that indicates the word "How"was not found.  But when you change it back to "East" that does work, so sounds like East is stil the first word in Col1 after removing the top 3 rows.

and if this gives an error

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"How", type any},

then you don't have a column named How.

Please supply a screenshot of your modified worksheet.

Phil

[edhans]

My solution does not rely on the data being in a specific format. It looks for the word East and sets that to the top row. It doesn't remove the top 3 rows by count @PhilipTreacy 

Now, if the word East is no longer in the first column, or anywhere, then my solution fails, but at some point there needs to be some consistency or logic somewhere. It would be some pretty complex M code to find a grid of data in an Excel sheet that:

1. Kept moving around (not in the same column or row) and
2. The "column headers" of said grid kept changing to where they were never the same on any column you coulld look for

Not sure how that would work, but I don't think that is your issue @acerNZ is it?

[PhilipTreacy]

Hi @edhans 

Yes sorry, AcerNZ had said they had 'modified relevant fields' and 'added another row in the excel sheet'.  At that point I had no idea exactly what they had done and was trying to convey that if they removed the word East or added rows above East, they'd get unexpected results because the code was looking for, and expecting, certain certain things to be in the sheet.