Data processing, all values in one column

[Canadf]

Hello, this is my first question.

I'm new with PowerBI, I'm still learning. 

Going straight into my problem:

I have a database where all the data introduced by the worker is saved into one column (field_value), and the other columns state info abou that data, like field_name (the name that field has in our form), step_name (the name the step containing that field has in our form), the job_name (the name of a single piece of paper that a worker fills), and many others, like the date and time it was filled.

The thing is, the stored date and time doens't work for segmenting he data, because each paper can be filled in two different days (even if it refers to only one day, this is because of the beggining time being somewhere before 23:00 of the previous day), so my main segmentation will be the Job Name, and a field filled with the DATE of that job. 

So instead of having a single row for each Job, containing with one column for each of the field_values introuced (for example, one column for color, one column for number, one column for worker), I have several rows repeating the job_name and specifying through the other columns to identify a single field_value as unique.

So my question is, how can I use relations, to relate several rows of field_value, to a single job_name and a DATE? Or even creating a new table, where all this data is converted to a single row per job_name, using the field_name as column names? (Dynamic columns hasn't quite worked or me, as I don't know how to fully use them)

Sorry if the question is a bit confusing, I can't really post my company's data as an example

[audreygerred · Accepted solution]

Hi! It sounds like you could use the Pivot option in Power Query. Here is a link to details about it. Pivot columns - Power Query | Microsoft Learn

[Canadf]

Hello, thanks for your response, but I had already tried it, and I just tried it again and it's not working. Columns aren't being generated correctly, and it's seems like too many data is created. Any way I could use relations to make this work?

[audreygerred]

Can you share an example or your data by uploading the pbix?

[Canadf]

I had to change data because of company policy.

Basically, there are two days in the date column, and each day there are several job_names, that are different job reports. Each job_name has several step_names, and each step_name has several field_values, but also, some field_values are in different step_names. 

asically the structure of our report is 

Job

     -Step

          +field 1

          +field 2

     -Step

          +field 3

          +field 1

https://drive.google.com/file/d/1psuJIU5vp9PZLKRq5r7NvZ4bhXi-TtWa/view?usp=drive_link

So I want to ideally transform each job_name into one row, with columns for each field_value, but having the same field name in different step names can dificult this. So I wondered if you could assimilate this using relations, so that when I segment any visual into the day (that is introuced as a field in Field_value,and therefore doesn't appear in every row), basically creating a relation between a Job_name and a day

[audreygerred]

If I understand you correctly, you want to split job_name apart, correct? To do this you can go into Power Query, click on that field and split the column. You can choose to split by delimiter and then choose : for teh delimeter and select once far left - this will put Recogida de Datos formato in one column and everything else into a second. Next, click on teh new column that was created and split that column, this time split by a space and do once as far left. This will give you the values of 120995, 144221, etc. in a column. Next click on the new column that should have ABC Dia: and the date in it. You can split this on space as far right as possible and it will give you your date in a column all by itself. Hope this helps!

[Canadf]

That is not what I asked, thank you anyways