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

Data Transformation Help - Rows to Columns

Hi,

 

Hope i can explain this properly. I'm trying to scrap some data that's in a table on our organisation webpage. I tried the Web data connector but this does not appear to work.

As a work around i thought i could copy the table information and place it into a csv/excel file and then have powerbi connect to that.

 

The issue i have is the data gets pasted in single rows. E.g.

 

Heading 1

Heading 2

Heading 3

Item 1.1

Item 1.2

Item 1.3

Item 2.1

Item 2.2

Item 2.3

 

There are three colums to the table.

 I was wonder can PowerBI convert the above single row list into a 3 column table i.e.

 

Heading 1  Heading 2  Heading 3
Item 1.1      Item 1.2     Item 1.3

Item 2.1      Item 2.2     Item 2.3

 

etc. 

 

An alternatively to possible get a macro do this prior to uploading into PowerBi.

 

Appreciate any comments on the above.

 

9 REPLIES 9
Highlighted
Super User III
Super User III

Hi @babz88,

 

I believe that this can be done in query editor.

 

@ImkeF can you help out on this one?

 

Regards,

 

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted

This works for me. 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTMnMS1cwVIrVQfCMUHjGYJ5nSWqugqGeITLHCJmDpMwIWZkRsjIjkLJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Custom2 = Source[Column1],
    Custom1 = List.Split(Custom2,3),
    Custom3 = Table.FromRows(Custom1)
in
    Custom3

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Frequent Visitor

Hi lmkef,

 

Thanks for getting back quickly. 

I've attempted but failed to try intergrate your code with the source data i'm connecting to..

 

I'm pulling the data from the following:

let
    Source = Excel.Workbook(File.Contents("C:\Users\tc8apz\OneDrive - LR\Work\1 ¦ TQM\1 ¦ IMS-GMS\updates\gms update - alternative.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
    Sheet1_Sheet

Any thoughts?

Highlighted

Yes, please check out my video here: http://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-co...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Community Support
Community Support

@babz88,

 

It's a little bit complicated, to achieve this, you may follow steps below:

 

Firstly, duplicate the column-> split the column(copy by Number of Characters).

1.PNG2.PNG 

 

Remove the useless columns and duplicate the table into three tables, in each table filter the copy column with 1, 2 and 3.

3.PNG4.PNG 

 

Add indexs in the three tables and merge the three tables based on index columns.

5.PNG 

 

Finally, click Use First Row as Headers, the result is like below:

6.PNG 

 

You may also refer to the appendix.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Highlighted

Hi, thanks for the suggestion. Unfortunately, didn't as the text values are bit more complex than just item 1.1, 1.2 etc. I only used that as an illustation. 

Highlighted
Frequent Visitor

Hi, unfortuntaly the link appears to be broken.. can you resend pls?

Highlighted

Edited the link in the post.

To be on the safe side, here it is again: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-c...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted

Share the actual text entries.  If Heading 1 is New Delhi and Item 1.1 is Road, how will Road be mapped to New Delhi?  Show a better example and the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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