Forum Discussion
Solved
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 use...
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
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. |
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
Anonymous
Thanks. A second solution to this particulr problem.
Nicely steped through in the PBIX file too. Appreciated.