Forum Discussion
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.
| 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:
- treat as JSON, then expand the column so that I get 12 columns,
- 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.....
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
11 Replies
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
Community Champion
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
- zaza
Resolver III
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:
