Data Formatting Question for Data scraped from the Internet

Hi all!

I am using a data scraper to pull info from the web and an struggling to transform it in a way that's easily read.

Right now the csv file comes out like this:

Title1 - data1
Title2 - data2
Title3 - data3
Title1 - data4
Title2 - data5
Title3 - data6

And then it repeats like that. What I want is to move the titles into the columns and then have the right side with all the data moved into the corresponding columns with the appropriate titles like

Title1 title2 title3
Data1 data2 data3
Data4 data5 data6

If anyone could give me some tips on how to transform the data I would appreciate it! I mostly just don't really know how to aggregate all the titles and get the relevant data into the corresponding column.

Thank you!

something like this?

Kinda like that but having each entry be under a single "Word Mark" column and so on and so forth - so it looks more like a table where each trademarks information is lined up in the same row.

post an example with real expected result (from your source data)

This is perfect! Thank you so much! How did you do this?

with Power Query

---

if the problem is solved, make me happy and hit Add Reputation (bottom left corner next to the post that was helpful)
and then mark the thread as SOLVED (top above your first post - Thread Tools)

Got it. I will see if I can mess around with power query to try to get something similar to what you did.

sure, here's some help if you need it : Power Query documentation

Hello! I know its been awhile but I was wondering if you could tell me why the power query you wrote only goes up to 195 records. I wasn't sure if I could write a loop for the split function which would make it go beyond 195 records or if I would have to manually type out a list like that for any of my documents with more than 195. I have a couple with 600+ and one with over 2700 records so it would be really helpful to figure out how to do it without manually typing in each one. THank you!

for reference, this is the function I am referring to:

= Table.SplitColumn(rc1, "list", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"list.1", "list.2", "list.3", "list.4", "list.5", "list.6", "list.7", "list.8", "list.9", "list.10", "list.11", "list.12", "list.13", "list.14", "list.15", "list.16", "list.17", "list.18", "list.19", "list.20", "list.21", "list.22", "list.23", "list.24", "list.25", "list.26", "list.27", "list.28", "list.29", "list.30", "list.31", "list.32", "list.33", "list.34", "list.35", "list.36", "list.37", "list.38", "list.39", "list.40", "list.41", "list.42", "list.43", "list.44", "list.45", "list.46", "list.47", "list.48", "list.49", "list.50", "list.51", "list.52", "list.53", "list.54", "list.55", "list.56", "list.57", "list.58", "list.59", "list.60", "list.61", "list.62", "list.63", "list.64", "list.65", "list.66", "list.67", "list.68", "list.69", "list.70", "list.71", "list.72", "list.73", "list.74", "list.75", "list.76", "list.77", "list.78", "list.79", "list.80", "list.81", "list.82", "list.83", "list.84", "list.85", "list.86", "list.87", "list.88", "list.89", "list.90", "list.91", "list.92", "list.93", "list.94", "list.95", "list.96", "list.97", "list.98", "list.99", "list.100", "list.101", "list.102", "list.103", "list.104", "list.105", "list.106", "list.107", "list.108", "list.109", "list.110", "list.111", "list.112", "list.113", "list.114", "list.115", "list.116", "list.117", "list.118", "list.119", "list.120", "list.121", "list.122", "list.123", "list.124", "list.125", "list.126", "list.127", "list.128", "list.129", "list.130", "list.131", "list.132", "list.133", "list.134", "list.135", "list.136", "list.137", "list.138", "list.139", "list.140", "list.141", "list.142", "list.143", "list.144", "list.145", "list.146", "list.147", "list.148", "list.149", "list.150", "list.151", "list.152", "list.153", "list.154", "list.155", "list.156", "list.157", "list.158", "list.159", "list.160", "list.161", "list.162", "list.163", "list.164", "list.165", "list.166", "list.167", "list.168", "list.169", "list.170", "list.171", "list.172", "list.173", "list.174", "list.175", "list.176", "list.177", "list.178", "list.179", "list.180", "list.181", "list.182", "list.183", "list.184", "list.185", "list.186", "list.187", "list.188", "list.189", "list.190", "list.191", "list.192", "list.193", "list.194", "list.195"})

maybe it will help How to iterate web result

This has some useful information, thank you! I am trying to incorporate the list.Max function the guy in the video uses but haven't had any luck so far. I will continue trying.

you can try this modified Power Query for other csvs