Separating data with multiple variables in one cell into multiple

[Anonymous]

Hello! I have a dataset with 3 types of data pulled from FOI requests and such that I would appreciate help with.

The data has 3 columns.

"Title" or the heading of the initial request

"Action" with a listing of the process related to it, such as an information request, copy, or advising a user. This column can contain multiple types of variables as the request can require many actions to be taken.

"Topic" , pertaining to what kind of information the request was about, such as legal, archive, other etc. This can also contain multiple of values in the same cell as the information provided can overlap.

The title field is currently of no interest to me as they are all unique, but I would like to view the other two fields and be able to better combine them, as currently PowerBI reads a field such as "archive other" as unique and separate from both the "archive" and "other" variables, when what I want it for it to count as both of them.

And this same row can contain multiple types of action, as well. Is it possible to either separate them easily as the dataset is reasonably large or even better have them register as I wish for them, so they would count separately, possibly with a delineator?

Below is an example of my data, apologies for the data not being in english. In total there are 6 actions and 5 types of topics.

Any and all help is extremely appreciated!

TITLE 1	Tiedonhaku  Käytön opastus	eduskuntatieto  oikeudellinen tieto
TITLE 2	Tiedonhaku  Käytön opastus	eduskuntatieto  oikeudellinen tieto  yhteiskuntatieto
TITLE 3	Tiedonhaku	eduskuntatieto  arkisto
TITLE 4	Tiedonhaku	eduskuntatieto  oikeudellinen tieto  yhteiskuntatieto
TITLE 5	Kopio, pdf  Käytön opastus	oikeudellinen tieto
TITLE 6	Kopio, pdf  Muu toimeksianto	oikeudellinen tieto  arkisto

[MFelix · Accepted solution]

Hi @Anonymous ,

Not really sure if I understand your data in the format you present it but is this a single column per row or do you have 3 columns?

Another question is how do you know what is the split between the several actions and topics? Is it based on the space between the words or are there any other way to limit those fields?

In the Query Editor you can do a split by delimiter, and instead of doing new columns you can do rows, believe that is what you need to do however if you can answer the questions above it woudl be easier to help you achieve the final result.