Data Transformation Help - Rows to Columns

[Anonymous]

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.

[v-yuta-msft]

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

 

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

 

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

 

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

 

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]

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. 

[Ashish_Mathur]

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.

[MFelix]

Hi @Anonymous,

I believe that this can be done in query editor.

@ImkeF can you help out on this one?

Regards,

MFelix

[ImkeF]

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

[Anonymous]

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?

[ImkeF]

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

[Anonymous]

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

[ImkeF]

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