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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
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?

 

 

6 REPLIES 6
Anonymous
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?

v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

What is the expected out put, like below?

vjaywmsft_0-1655454689946.png

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
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,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
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)?

 

bvanormer_0-1656093100172.png

 



Thank you for your patience. 

Best Regards,

 

Brendan 

Anonymous
Not applicable

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

 

Cordially,

 

B. van Ormer

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.