cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
babz88
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
v-yuta-msft
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.

 

 

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. 

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/
MFelix
Super User
Super User

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



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

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

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

babz88
Frequent Visitor

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

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.