cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

@Anonymous,

 

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.

 

 

Anonymous
Not applicable

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 @Anonymous,

 

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.