Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 - 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.
Greg,
Would this be an index column where each column has a unique value?
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |