Forum Discussion
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
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 |
Community ChampionHi 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?
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
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
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.