CSV Second Row is the Header and Split a collum based off the tabs into multiple columns

[icassiem]

Good day,

I receive an automated CSV file daily on a SH Document library location. 

1. The first row is a general message; when importing via WEB URL from SH, how do I make the second row my header?

2. I also have a dimensional column that I want to split into multiple columns based on the row/cell number of "TAB" (Country (O TAB), Region (1 TAB), City (2 TABS)). Here is an example of the value

   Collumn: Location

   Row1: South Africa

   Row2: "TAB"   Western Cape

   Row2: "TAB" "TAB"    Cape Town

Please Help with any ideas

Regards

[Akash_Varuna · Accepted solution]

Hi @icassiem Try this please 

1. Make Second Row the Header:

   • Remove the first row in Power Query (Remove Rows > Remove Top Rows).

   • Promote the second row as headers (Transform > Use First Row as Headers).

2. Split "Location" Column by "TAB":

   • Replace "TAB" with a delimiter like | (Transform > Replace Values).

   • Split the column by the delimiter (Transform > Split Column > By Delimiter).

   • Trim spaces (Transform > Trim).
     If this post helped please do give a kudos and accept this as a solution
     Thanks In Advance

[icassiem]

Thank You so much @Akash_Varuna , this worked nicely 🙂