Transposing Text Data

[Anonymous]

Hello,

I'm trying to transpose some data from a text file and I haven't had any luck finding a solution. I want to generate a standard table for this data. So every row becomes a column and then the data is filled in with a new row. So far all I get is one row with hundreds of columns.

The data is a text file that looks like this:

Value1 Value2
----------------
Device ID: Value1
IP Address: 192.168.0.0
IPv6 Address: 0000::0000:0000:0000:0000
Platform: Value2, Abilities: Value3
Interface: Value5, Address ID: Value4
IP Platform Addresses:
IP Address: 192.168.0.0
----------------
Device ID: Value1
IP Address: 192.168.0.0
IPv6 Address: 0000::0000:0000:0000:0000
Platform: Value2, Abilities: Value3
Interface: Value5, Address ID: Value4
IP Platform Addresses:
IP Address: 192.168.0.0
----------------
Device ID: Value1
IP Address: 192.168.0.0
IPv6 Address: 0000::0000:0000:0000:0000
Platform: Value2, Abilities: Value3
Interface: Value5, Address ID: Value4
IP Platform Addresses:
IP Address: 192.168.0.0
----------------
Device ID: Value1
IP Address: 192.168.0.0
IPv6 Address: 0000::0000:0000:0000:0000
Platform: Value2, Abilities: Value3
Interface: Value5, Address ID: Value4
IP Platform Addresses:
IP Address: 192.168.0.
----------------
Device ID: Value1
IP Address: 192.168.0.0
IPv6 Address: 0000::0000:0000:0000:0000
Platform: Value2, Abilities: Value3
Interface: Value5, Address ID: Value4
IP Platform Addresses:
IP Address: 192.168.0.0
----------------
Value1 Value2
----------------
Device ID: Value1
IP Address: 192.168.0.0
IPv6 Address: 0000::0000:0000:0000:0000
Platform: Value2, Abilities: Value3
Interface: Value5, Address ID: Value4
IP Platform Addresses:
IP Address: 192.168.0.0

What I'm looking for is something like this:

MainHeader	Device ID	IP Address	IPv6 Address	Platform	Abilities	Interface	Address ID	IP Platform Addresses
Value1 Value2	Value1	IP	IP	Value2	Value3	Value5	Value5	IP
	Value1	IP	IP	Value2	Value3	Value5	Value5	IP
	Value1	IP	IP	Value2	Value3	Value5	Value5	IP

I'm sure this is pretty straight forward but I'm not having any luck. 

Also, is there a good book for learning how to transpose data? the one I'm currently reading is DAX only so it doesn't cover it.

Thanks.

[Greg_Deckler · Accepted solution]

@Anonymous - So I believe @ImkeF 's general solution to this is to create a counter column that numbers your rows with repeating so like MOD 7 essentially. Then you pivot/transpose based upon that column.

[Anonymous]

Greg,

Would this be an index column where each column has a unique value?

[Greg_Deckler]

@Anonymous - Yes, I would start with an Index column. See this

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/