Split dictionary values into columns

[alpb]

I would like to split these dictionaries one into its own column, with only the values inside.

For instance:

No.  | square | insights | api_token | messaging | reporting

1      |  null      | null         | null       | null             | null

2      |  false     | true        | false     | true             | false

3.     |  false     | true        | false     | true             | null

The additional problem that adds to complexity is that not all of the rows start with the same value. 

Some values start with "square", some others start with "messaging", and so on.

[jdbuchanan71 · Accepted solution]

@alpb 

You can do this in power query by 

1. remove {}

2. split column on ,

3. add an index row

4. unpivot all but the index row

5. remove the Attribut column

6. split the Value on :

7 pivot value.1 column showing value.2 without any aggregation.

I have attached my sample file for you to look at.  Just right click on the table and select "edit query" to see the steps.

[jdbuchanan71 · Accepted solution]

@alpb 

You can do this in power query by 

1. remove {}

2. split column on ,

3. add an index row

4. unpivot all but the index row

5. remove the Attribut column

6. split the Value on :

7 pivot value.1 column showing value.2 without any aggregation.

I have attached my sample file for you to look at.  Just right click on the table and select "edit query" to see the steps.

[alpb]

4. unpivot all but the index row --> in this step, if I have other columns, let's say "name, job title", do I also include these 2 columns in the unpivot stage?

could you please explain more about step 7? i am not sure how to do that. There is a pivot column functionality, but how do I choose to show value or do one without any aggregations?

[v-kelly-msft · Accepted solution]

Hi  @alpb ,

unpivot all but the index row --> in this step, if I have other columns, let's say "name, job title", do I also include these 2 columns in the unpivot stage?

Yes.

could you please explain more about step 7? i am not sure how to do that. There is a pivot column functionality, but how do I choose to show value or do one without any aggregations?

Pivoting column Value.1 according on values from Value.2 using "not aggregated" based on index column:

Then you will get the result you need.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Mohammad_Refaei]

In power query split column by delimiter... you may share sample data in text format to send you a sample file .

[alpb]

the split column by delimiter function will only result like this
No. column_name1 column_name2
1      search2: true        app_shell: true

2      square:false          insights: true

[Mohammad_Refaei]

Yes... we will need to do further processing like pivoting unpivoting