How to pivot (or sort) data into four columns with headers from a column with 4 repeating values.

[Anonymous]

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?

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

Hi @Anonymous ,

Please take a look at the pbix file.

Best Regards,

Jay

[Anonymous]

Hi Jay what you certainly did works. To be honest, I stared at this for over 15 minutes so I'm going to ask for help or a better print guide than the link below to perform the "filtered rows" step.

Filter by values in a column - Power Query | Microsoft Docs

I see that you did apply the filter to break it up into different tables. I see that works. It looks like there are multiple ways to solve this problem and would like to learn these functions.

1st step stuck on:

How do I pull up this dialog box from scratch (not editing yours)?

Thank you for your patience. 

Best Regards,

Brendan 

[Anonymous]

Did my due diligence and looked at Microsoft Document, LinkedIn Learning, and Udemy. Did I word this request properly?

Cordially,

B. van Ormer