cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Switch where cell contains json data

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.

 

CustomerPeak 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)

 

CustomerBusiness Peaks
XJan Feb Jul
YJul

 

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.....

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Switch where cell contains json data

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

View solution in original post

Highlighted
Super User V
Super User V

Re: Switch where cell contains json data

Hi @antonyf 

 

I've adjusted the file to accommodate an extra table 

image.png

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn



View solution in original post

11 REPLIES 11
Highlighted
Super User V
Super User V

Re: Switch where cell contains json data

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

 

Highlighted
Resolver II
Resolver II

Re: Switch where cell contains json data

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

Highlighted
Regular Visitor

Re: Switch where cell contains json data

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.

 

CustomerTownBusiness PeaksAttribute XAttribute Y
AHightonJan Feb Jul  
BLowtonJan  
CMidtonFeb  

 

 

Highlighted
Super User V
Super User V

Re: Switch where cell contains json data

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

 

Highlighted
Regular Visitor

Re: Switch where cell contains json data

@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

 

CustomerAccount TypeTownPeak Buiness Months
XKeyUpton{"0":348,"1":349,"2":354}
YBasicDownton{"0":354}
ZBasicMidton{"0":355,"1":356}

 

End Point

CustomerAccount TypeTownPeak Business Months
XKeyUptonJan Feb Jul
YBasicDowntonJul
ZBasicMidtonAug Sept

 

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

 

 

 

 

 

Highlighted
Super User V
Super User V

Re: Switch where cell contains json data

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

 

Highlighted
Regular Visitor

Re: Switch where cell contains json data

@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

 

IDMonth Name
348Jan
349Feb
350Mar
351April
352May
353Jun
etcetc.
  
  

 

Highlighted
Anonymous
Not applicable

Re: Switch where cell contains json data

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

View solution in original post

Highlighted
Super User V
Super User V

Re: Switch where cell contains json data

Hi @antonyf 

 

I've adjusted the file to accommodate an extra table 

image.png

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn



View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors