Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.....
Solved! Go to 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
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:
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 |
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?
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'
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. |
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |