cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Column header and data in same row

I ve this type of records:

date=2020-07-28 time=11:35:29 user="user001" msg="Some String With Spaces" logid=100
date=2020-07-28 time=9:15:10 user="user002" msg="Another Text" logid=102

date=2020-07-27 time=10:55:23 user="user001" msg="The quick brown" logid=105

I want to split first line, and use the text before the = sign as headers and then split every row and use the text after the = sign as data. This is the expected result

date                  time         user         msg                                     logid

2020-07-28     11:35:29    user001     Some String With Spaces     100
2020-07-28     9:15:10      user002     Another Text                         102

2020-07-28     10:55:23    user001     The quick brown                   105

Ane idea how to resolve this?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

@BahntjeRuben 

I saved your data as a text file and imported to into PBI, and did the transformation. Please check the steps and codes. This is dynamics, any # of columns and rows will work

 

You can download the file: HERE

 

Annotation 2020-07-29 155847.png

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn


View solution in original post

2 REPLIES 2
Highlighted
Solution Sage
Solution Sage

Must be an easier way than this but you could use Power Query:

 

I'm presuming "date=2020-07-28 time=11:35:29 user="user001" msg="Some String With Spaces" logid=100" is in one cell? if so, 

 

Split by delimited "="

Then split "time", "user", "msg" and "logid" by delimited by Right most "Space". 

 

Karlos_0-1596025376456.png

The just remove the redundant columns and rename the remaining. 

Karlos_1-1596025413446.png

 

 

 

 

 

 

 

 

Highlighted
Community Champion
Community Champion

@BahntjeRuben 

I saved your data as a text file and imported to into PBI, and did the transformation. Please check the steps and codes. This is dynamics, any # of columns and rows will work

 

You can download the file: HERE

 

Annotation 2020-07-29 155847.png

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn


View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors