Forum Discussion
How to pivot (or sort) data into four columns with headers from a column with 4 repeating values.
Hello community,
I spent a month on the intermediate to advanced tutorials and have been lurking in the forums for a couple weeks and cannot find the answer after fiddling around.
Data is parsed from a txt file using comma delimiters and I was able to make to the image attached:
How can I:
Take a column with 4 repeating cells : (Date, From, To, Total Seconds)
Pivot/sort/transform so that those 4 repeating cells become column names
Data from the rest of the table is filed under those 4 columns named: (Date, From, To, Total Seconds).
As you can see in the image I got as far as using the modulo command to number each 0, 1, 2, 3, but I don't follow after that.
Image
*cropped to keep data hidden*
All of the examples I have found have examples that are too "pre-sorted" when getting inputs of text data.
Can someone please help find the right command sequence or a tutorial link or point in the right direction?
6 Replies
Hi Anonymous ,
What is the expected out put, like below?
Could you please share some sample data?
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Best Regards,
Jay
Hello Jay,
Here is an example (though in excel) of how I need to transform the data in PowerBI. Taking each repeated set of four output values and transposing them under 4 column headers (Column 1.1 is only transposed once as a header).
I don't know how to cut out this manual Excel transposition and looking to see how PowerBI would do it directly.
This is very difficult problem to solve but seems like something one should be able to do. I am transforming from a sample sheet (text) file as I am using "get data" to pull in a folder.
1. I was able to filter the data, create 4 different tables for the Date, From, To, and Totals Seconds column. I merged queries to put the different table data back in to a master table but there are no values as it just says "table, table, table" in the cells. How do I get real data and where did I go wrong?
2. The relationships in the model tab/page appear broken with the space between & 2 parenthesis on the "relationship line"
Can someone help or is there a better way when mining from full txt files (all using the same format) to use the data?