Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Not applicable

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 transform data powerbi.PNG



*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?



Not applicable

transform data.JPGtransform data3.JPG


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?

Community Support
Community Support

Hi @Anonymous ,


What is the expected out put, like below?


Could you please share some sample data? 


Best Regards,


Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Not applicable

Excel example of desired PowerBI command.JPG


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.



Hi @Anonymous ,


Please take a look at the pbix file.


Best Regards,


Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Not applicable

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,



Not applicable

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




B. van Ormer

Helpful resources


Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.