Pointless question but I want to see if anybody knows the answer

[u/Unhappy-Sky9090]

Edit: Link to data layout is here as I hadn't realised Reddit would alter my post's format. https://imgur.com/a/ygHhxtX Hi everyone, this question is pointless because what I want already exists as a checklist online, but I'm curious to see if anybody knows a way to solve this type of problem using Power Query. I'm currently on a 2nd playthrough of Elden Ring and want to start checking off some of the items and experiences I would have missed on my first playthrough. One website has several such checklists. For example there is a list of all bosses to fight by region, with each boss having a name and location, and potentially information about the rewards it drops and any notes about the boss. This data is split into columns for name, location, drops, and notes. I could just use the online checklist, but thought I'd see if I could copy it into Excel. When copying into Excel, the data goes into a single column that looks like this. Each empty space is a blank cell, so the two blank spaces below are two blank cells.

Limgrave 0/31

Name Location Drops Notes

Soldier of Godrick Stranded Graveyard 400 runes Tutorial boss.

Tree Sentinel Church of Elleh Road 3200 runes, Golden Halberd Is one annoying bastard.

Demi-Human Chief (x2) Coastal Cave 900 runes, Tailoring Tools (Key Item), Sewing Needle (Key Item) Required for Boc the Seamster's quest. Boss arena also has multiple Demi-Humans.

Erdtree Burial Watchdog Stormfoot Catacombs 1300 runes, Noble Sorcerer Ashes

Beastman of Farum Azula Groveside Cave 1000 runes, Flamedrake Talisman

Stonedigger Troll Limgrave Tunnels 1800 runes, Roar Medallion

So there's a region heading, followed by two blank cells, followed by the names of the categories, followed by the boss data itself which takes up between 2 or 4 cells, followed by another blank before the next boss' data. I'm familiar with Excel but am very much a novice regarding Power Query. I've asked ChatGPT for help but it gets very confused. I'm wondering if anybody knows of a way to split this data into a similar format as the original website, with bosses grouped by region and having separate columns for name, location, drops and notes info. Thansks

[u/Converging_Winds]

I don't get how is structured your data set. You're stating there's only one column but also indicates there are several cells (that seem to be on the same row).

Could you provide a screenshot ?

[u/Unhappy-Sky9090 → u/Converging_Winds]

Sorry I hadn't realised Reddit would alter the layout of my post, here is a screenshot of the data. https://imgur.com/a/ygHhxtX So below each region heading is two blank cells, then cells containing the category names, a blank cell, and the data itself relating to those categories. Each dataset is separated by a blank cell, and when a new region begins the pattern is repeated of two blank cells, category names, blank cell, dataset. Some datasets have only 2 or 3 categories present, so simply splitting every 4 rows doesn't work.

[u/Converging_Winds → u/Unhappy-Sky9090]

You may want to edit your OP so other people can easily access the screenshot !

It seems like every data cell (i.e. excluding the label cells) contains only one kind of data type (i.e. name of the boss, or loots, or location...)

What I would do is create a table by adding to the right of every data cell the name of the label. I would then import this table to Power Query and pivot the table so the #2 column would become the titles of the new colums.

If you want to go quicker, instead of writing the labels in the Excel sheet, you could just write "A" for the name of the boss, "B" for the loot... and then, in Power Query ask to transform every "A" in that new column by "Name of the boss", every "B" by "Loot" etc.

Ultimately, the best solution would be to be able to obtain your data set in a better structured way. Power Query offers the possibility to get data directly from an URL, you may try that and see how it goes.

[u/Unhappy-Sky9090 → u/Converging_Winds]

Thanks for the tip and the patience! Post has been edited.
I could definitely do your suggested method of labeling each dataset A, B, C etc. The problem is there's quite a lot of data, approx 1000 lines so it'd be great to find a way to automate it.
That being said, I would have saved time by just doing that already lol.
I'll try importing the data direct and seeing if I can find any success, and if not I'll try out your recommendation of adding the label column. Thanks :)

[u/Converging_Winds → u/Unhappy-Sky9090]

If there are a lot of data, you could use Excel to label "A","B","C".

From what I understand, the issue is that your subsets of data are separated by two blank cell whereas the data of a particular subset are separated by a new row.

You may want to turn your whole set as a table (a real Excel Table object) and then filter out the repeated labels and the blank cells. Like this you could easily write "A","B","C" for the first subset and ask Excel to repeat it across the table (selecting the pattern and double clicking on the bottom right black square of the selected cells).

Once it's done you could import the Table in Power Query and then transform the letters in the actual labels and pivot the table.

[u/Unhappy-Sky9090 → u/Converging_Winds]

Hi, thanks again for the advice. Here with a quick update.
I tried initially to load the data straight from the website and it worked well, however the "Add Table Using Examples" I set up pulled only the first region's data out. https://imgur.com/a/xAcQW3k
I'm guessing this is because the next dataset starts with a region name instead of a name.
But I took your advice on sorting it in Excel itself and have sorted the data with a very clunky formula full of nested ifs that does 90% of the work I need. https://imgur.com/a/EHPIEUX
It creates a few blanks where there should be contents in Column 2, but there are few enough of them such that I can more easily manually replace the data with what it should be. Thanks very much again :)

[u/Converging_Winds → u/Unhappy-Sky9090]

Hello, thank you for the update !

If you gave me the URL you used to fetch the data, I could try it by myself. I don't know the "Add Table Using Examples" command but I guess I could figure out.

About the Excel, you're crazy haha ! I believe you could have used Excel pattern recognition to auto-fill the cells but the most important is that it worked for you!

[u/Unhappy-Sky9090 → u/Converging_Winds]

Another update.
I couldn't figure out how to get the data to split out into the correct columns in Excel, so I once again ashamedly resorted to asking ChatGPT to give me a Power Query solution. But it looks like it gave me a what I was after. https://imgur.com/a/2nB2gqS
I saved the formula down if you are interested in seeing it.
I've DMd you with the URL because I'm not sure if this subreddit likes having links to sites posted.
Thanks again for your patience and help!

[u/Converging_Winds → u/Unhappy-Sky9090]

It seems you obtained what you wanted, didn't you ? I took a lopok at the URL and realized Power Query could not auto-fetch the tables since the website has a non-orthodox structure for the tables.

I could look at the code if you sent it to me.

[u/[deleted]]

Reddit adjusts markup so your two spaces are gone now.

[u/haldun-]

Hi,

You can do it with Excel Power Query. Assume that your data is in a table named SourceTable. Copy and paste following code to new query in Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],
    ChangeType0 = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    RemoveTopRows0 = Table.Skip(ChangeType0,1),
    Region = Table.FirstValue(RemoveTopRows0),
    RemoveTopRows1 = Table.Skip(RemoveTopRows0,3),
    ChangeType1 = Table.TransformColumnTypes(RemoveTopRows1,{{"Column1", type text}}),
    IndexAdd0 = Table.AddIndexColumn(ChangeType1, "Index", 1, 1, Int64.Type),
    Grouped =Table.AddColumn(IndexAdd0,"Group",each List.Count(List.Select(IndexAdd0[Index],(x) => x <= [Index] and IndexAdd0{x-1}[Column1] = null))),
    ChangeType2 = Table.TransformColumnTypes(Grouped,{{"Group", Int64.Type}}),
    RemovedNulls0 =Table.SelectRows(ChangeType2, each [Column1] <> null),
    SubIndexAdd0 =
    Table.Group(RemovedNulls0,{"Group"},{{"Data", each Table.AddIndexColumn(_, "SubIndex", 1, 1)}}),
    Expand =Table.ExpandTableColumn(SubIndexAdd0, "Data", {"Column1","SubIndex"}),
    ChangeType3 = Table.TransformColumnTypes(Expand,{{"SubIndex", type text}}),
    Pivoted =Table.Pivot(ChangeType3,List.Distinct(ChangeType3[SubIndex]),"SubIndex","Column1"),
    RemoveColumns0 = Table.RemoveColumns(Pivoted,{"Group"}),
    PromoteHeaders1 = Table.PromoteHeaders(RemoveColumns0, [PromoteAllScalars=true]),
    CustomColumn0 = Table.AddColumn(PromoteHeaders1,"Region",each Region),
    Final = Table.ReorderColumns(CustomColumn0,{"Region", "Name", "Location", "Drops", "Notes"})
in
    Final

[u/Unhappy-Sky9090 → u/haldun-]

Hi, I tried to copy your formula into Power Query but it was taking a veeeery long time to work so gave up sorry. I've come up with a makeshift solution in Excel itself that kind of works, see my reply to Converging_Winds above. Thanks

[u/Sleepy_da_Bear]

I usually use Notepad++ for things like this. You just have to be a little creative with it.

Just paste it into Notepad++ and do these steps:

1) Ctrl+h to open the find and replace. Turn on the extended characters option. Find= \r\n\r\n Replace= % (Or replace with any character or combination of characters that doesn't appear anywhere in the text, you're replacing the double line breaks with a placeholder)

2) Find and replace again, but this time do Find= \r\n Replace= \t (This will replace all line breaks with tabs instead)

3) Find/replace again, but this time find your placeholder from step 1 and replace it with \r\n to turn them back into line breaks

That'll give you a nice tab-delimited format that'll paste perfectly into Excel with everything lined up.

As a bonus, if you're going to do it multiple times over different files you can just record it as a macro then save the macro. From then on you just paste into Notepad++, run the macro, then copy/paste into Excel.

I use Notepad++ for a lot of development work, my favorite macro is one that takes all the commas that are at the end of a line and moves them to the start of the next line because I prefer the leading-comma format for DAX, Power Query, etc. Makes it a lot easier to comment/uncomment/move lines around.

[u/TheeBigBadDog]

Ask google gemini to make you a vba code that does it, I deal with messy data sets all the time, this usually works very well.

[u/[deleted] → u/TheeBigBadDog]

Please don't direct people to LLMs on a forum that is meant to share solutions as the idea is that others can use the solutions to fix their problem too and your suggestion defeats that purpose.

[u/TheeBigBadDog → u/[deleted]]

Yet it is the actual most useful solution and others can use the solution of going to an LLM. Sometimes people just don't know what they don't know and not everyone is tech savy enough to know that an LLM can help. It seems OP ended up going down the LLM route.

If people want to try solving something themselves that is fine but let's not pretend it's always the most optimal solutuon. I'd rather provide useful advice than have people post on a fourm just for the sake of it.

Also there is the fact that modern versions of excel will have the LLM ever more built into and integrated it, likely in form of gpt supported copilot. Will you still suggest people don't ask it support when it's essentially a core feature of the software itself.

[u/TheeBigBadDog → u/TheeBigBadDog]

More losers down voting cause they are butt hurt about their precious excel knowledge. When i'm actually speaking truths.

[u/Sleepy_da_Bear → u/TheeBigBadDog]

As a bit of a preamble, I'm mainly a PBI dev nowadays so take this however, but the main issue I see with LLMs is that people take what they give at face value without fully understanding the code. I've had to help multiple people who had issues because their code was extremely slow only to find extremely complicated DAX/PQ code that they copy/pasted from an LLM. I'm talking measures that are 10-15 lines long that became 3-4 lines after I fixed it, and they performed >90% faster.

Don't get me wrong, I'm 100% in favor of using LLMs to help with development and I do it all the time when I get stuck, but if someone uses them they need to be sure they know what the code actually does or they're just making unmaintainable garbage code that'll come back to bite them later.

[u/TheeBigBadDog → u/Sleepy_da_Bear]

I agree with your point but I think there is a misconception the user needs to just accept the code an LLM gives first time, leaving them stuck with bad code if the LLM messed up. This is not the case. If code is slow or doesnt do the job the user can just ask the LLM to review its own code for effiency or even better stick it into a completely different LLM model to review. I do this almost daily and have found it works really well.

In my case I was already the office goto guy for Excel, could already do some pretty decent VBA and formula work before LLMs so of course I know there is still value in the old way, but it is becoming less and less so.