PowerQuery - Importing Txt Data Files & Parsing Columns

[Bullstrik1]

Hello Everyone!

For starters i'm quite new in PowerQuery/powerpivot although i'm faily familiar with Excel and VBA, so excuse me if this question been asked before.

I'm currently dealing with Txt data files with a particularity: this files are composed with several variables/fields and the file is automatically generated (via SQL i belive) with data of those fields alternatively in different rows. Below i post an exemple of the "header" of this files:

V1 V2 V3 V4 V5 V6 V7
V8 V9 V10
------------------------------ ------------------------------ ----------------- ----------- ------------------------------------------------------------ ------------------------------ -------------------------------------------------- --------------------
-------------------- -------------------- ----------------------------------------

I don't know if i'm making my self clear enough, so pls let me know if i'm not. I'll try to explain again.
Does someone know how would i manage to play arround in powerquery, in order to parse this data correctly, i mean, with data of a single registry in the same row and separated by the correct columns?

Tkx in advance for your help.

 

[scottsen]

The trick I read about in Ken Puls new book would be to add an identity column (auto generated column that just adds 1, 2, 3, ... to rows). Then you can use that to "know" you are on an even vs odd row.

Like, maybe an the even rows (v8,v9,v10), you copy the data out to a new column (what would line up past the V7), ... then that lets you unpivot?

 

[Bullstrik1]

The trick I read about in Ken Puls new book would be to add an identity column (auto generated column that just adds 1, 2, 3, ... to rows). Then you can use that to "know" you are on an even vs odd row.

Like, maybe an the even rows (v8,v9,v10), you copy the data out to a new column (what would line up past the V7), ... then that lets you unpivot?

Hi Everyone!

Tank you for your feedback scotten! And sorry for replying so late.
Seems like an interesting aproach, i must try it.