Switch where cell contains json data

[antonyf]

Hi

I wonder if anyone has an elegant solution to the following.

I have a table that contains a column of JSON data indicating which months a customer has thier peak business months. 

I want users to be able to filter customers who's Peak Business Month contains any specific month/months.

When imported and before treating the column as JSON the data looks like this, where there can be upto 12 values.

Customer	Peak Buiness Months
X	{"0":348,"1":349,"2":354}
Y	{"0":354}

the cell in question suggests the customer has peak bsuiness months of Jan, Feb and Jul

End Result Required (for the example above)

Customer	Business Peaks
X	Jan Feb Jul
Y	Jul

My instinct is to:

1. treat as JSON, then expand the column so that I get 12 columns,
2. then to merge the data so I end up with a column that looks like this (where there are 3 peak business months)

The column could contain between 0 and 12 values for each row of data.

Peak Months

352 353 358

3. From there I'm thinking of a custom column using Switch to provide a column 

Business Peaks=switch(348, "Jan",349,"Feb",350,"Mar",351,"Apr",352,"May",353,"Jun",354,"Jul",355,"Aug",356,"Sept",357,"Oct",358,"Nov",359,"Dec")

The Problem

My problem is that the Switch function only works effectively where a Merged cell at step 2 has only one value.

The only way I can think of dealing with this is to switch the values on each of the 12 columns of expanded JSON data before the merge. Then to hide everything but the final merged column from the end user.

Solution Required
Is there a variant on Swith that would pick up multiple values from the switch statemtment? 

Is there a more elegant alternative?

Hope someone can help.....

[Anonymous · Accepted solution]

Paste into the advanced editor and that's it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUfJOrQSSoQUl+XlAujpGySBGycrYxEInRskQzLIEsoxALFOTWqVYnWilSKA6p8TizGQg7ZJfnoeqE6YqCkmVb2YKmiJTmPGmZkDlsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Account Type" = _t, Town = _t, #"Peak Buiness Months" = _t]),
    #"Parsed JSON" = Table.TransformColumns(Source,{{"Peak Buiness Months", Json.Document}}),
    #"Expanded Peak Buiness Months" = Table.ExpandRecordColumn(#"Parsed JSON", "Peak Buiness Months", {"0", "1", "2"}, {"0", "1", "2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Peak Buiness Months", {"Customer", "Account Type", "Town"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MonthNumber", each [Value] - 348 + 1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Peak Month", each Date.ToText( #date(2020, [MonthNumber], 1), "MMM" )),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"MonthNumber"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns2", {"Customer", "Account Type", "Town"}, {{"Peak Months", each (_)[Peak Month], type table [Peak Month=text]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Peak Month", each Text.Combine( [Peak Months], " " )),
    #"Removed Columns3" = Table.RemoveColumns(#"Added Custom2",{"Peak Months"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns3",{{"Peak Month", "Peak Months"}})
in
    #"Renamed Columns"

Best

D

[zaza]

After merging the data you want to replace the values inside the M Query. You can do that one by one, or by writing a custom function. A good step by step solution is here:

https://www.youtube.com/watch?v=MLrRlPh_ZFQ

[Mariusz]

Hi @antonyf 

Please see the attached file with Power Query steps to transform JSON into a tabular format.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[antonyf]

Thanks @Mariusz 

I can see where you are heading with that and it works on certain levels.

However, I dont' think it will allow me to display a single cell containing all the peak business months for a given customer that sits in the original table carrying columns for many other customer attributes.

Customer	Town	Business Peaks	Attribute X	Attribute Y
A	Highton	Jan Feb Jul
B	Lowton	Jan
C	Midton	Feb

[Mariusz]

Hi @antonyf 

Sorry but I'm struggling to understand what you need, can you provide a sample for both tables and explain how they relate and explain what outcome you are expecting based on this sample?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[antonyf]

@Mariusz 

Whilst your proposed solution does provide me with an entension 

I suppose what I intially want to do is extract the Month IDs from the JSON array column and replace then with the Month Name.

Starting Point

Customer	Account Type	Town	Peak Buiness Months
X	Key	Upton	{"0":348,"1":349,"2":354}
Y	Basic	Downton	{"0":354}
Z	Basic	Midton	{"0":355,"1":356}

End Point

Customer	Account Type	Town	Peak Business Months
X	Key	Upton	Jan Feb Jul
Y	Basic	Downton	Jul
Z	Basic	Midton	Aug Sept

From there a contains filter can be applied to column 'Peak Business Months'

[Mariusz]

Hi @antonyf 

Please see the attached file with a solution.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[antonyf]

@Mariusz 

Temptingly close... but your solution does not reflect values in the corresponding JSON arrary correctly. 

It simply looks to be counting the number of responses in the JSON arrary, then simply inserting the same number of month names starting at Jan.

To resolve this... 

How can I adapat the line in your file (see below) to look up the correct month name for thevalues in the JSON array?

each Text.Start( Date.MonthName( #date( 1, Number.FromText( _ ) + 1, 1 ) ), 3

Lets assume I carry these values in a table titled Peak Months

ID	Month Name
348	Jan
349	Feb
350	Mar
351	April
352	May
353	Jun
etc	etc.

[Mariusz · Accepted solution]

Hi @antonyf 

I've adjusted the file to accommodate an extra table 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[antonyf]

@Mariusz 

Thanks for this. An elegant solution.

Note to future users: This variation on the solution requires an addtional table in the model, whilst @Anonymous's solution which also works involves addtional manipulations, but does not require the second table.

Well done both.

[Anonymous · Accepted solution]

Paste into the advanced editor and that's it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUfJOrQSSoQUl+XlAujpGySBGycrYxEInRskQzLIEsoxALFOTWqVYnWilSKA6p8TizGQg7ZJfnoeqE6YqCkmVb2YKmiJTmPGmZkDlsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Account Type" = _t, Town = _t, #"Peak Buiness Months" = _t]),
    #"Parsed JSON" = Table.TransformColumns(Source,{{"Peak Buiness Months", Json.Document}}),
    #"Expanded Peak Buiness Months" = Table.ExpandRecordColumn(#"Parsed JSON", "Peak Buiness Months", {"0", "1", "2"}, {"0", "1", "2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Peak Buiness Months", {"Customer", "Account Type", "Town"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MonthNumber", each [Value] - 348 + 1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Peak Month", each Date.ToText( #date(2020, [MonthNumber], 1), "MMM" )),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"MonthNumber"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns2", {"Customer", "Account Type", "Town"}, {{"Peak Months", each (_)[Peak Month], type table [Peak Month=text]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Peak Month", each Text.Combine( [Peak Months], " " )),
    #"Removed Columns3" = Table.RemoveColumns(#"Added Custom2",{"Peak Months"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns3",{{"Peak Month", "Peak Months"}})
in
    #"Renamed Columns"

Best

D

[antonyf]

@Anonymous 

Thanks. A second solution to this particulr problem. 

Nicely steped through in the PBIX file too. Appreciated.