cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
babz88 Frequent Visitor
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
Super User

Re: Data Transformation Help - Rows to Columns

Hi @babz88,

 

I believe that this can be done in query editor.

 

@ImkeF can you help out on this one?

 

Regards,

 

MFelix



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

Proud to be a Datanaut!




Super User
Super User

Re: Data Transformation Help - Rows to Columns

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

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




babz88 Frequent Visitor
Frequent Visitor

Re: Data Transformation Help - Rows to Columns

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?

Super User
Super User

Re: Data Transformation Help - Rows to Columns

Community Support Team
Community Support Team

Re: Data Transformation Help - Rows to Columns

@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.

 

 

babz88 Frequent Visitor
Frequent Visitor

Re: Data Transformation Help - Rows to Columns

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. 

babz88 Frequent Visitor
Frequent Visitor

Re: Data Transformation Help - Rows to Columns

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

Super User
Super User

Re: Data Transformation Help - Rows to Columns

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...

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Data Transformation Help - Rows to Columns

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.