Forum Discussion
zeitgeist
8 years agoRegular Visitor
Funnel Chart with filtered months
Hi, I am a bit new to PowerBI but not to charts, dashboards in general. I have the following table format: Date Accessed Purchased Paid 01/01/2018 526 91 50 02/01/2018 823 164 7...
- 8 years ago
v-frfei-msft
Community Support
8 years agoHi zeitgeist,
Based on my test, we can take the following steps to meet your requirement.
1, Enter the data as you shared and add a custom column in power query.
Date.ToText([Date],"MMMM")
2. Group the table by the new custom column.
3. DemoteHeaders - Transposed - Promoted headers , then we can get the result as we need.
Also M code in the Advanced editor for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFdDsQgCITv0ucm8qtwlmbvf40VKm6b7AuafMMw4nUd2LARoB3nodRndYwrHJ8zIBU04lmxy6xDFuWizjAr3ffqlaJEEM5oYWGFtTD2PuJgjclj4b6xWnZjiNQXHoVHpsaMt4PZjq0ZWzJ29XrR3jFzxWAEXRhhe3vMpOzG+YbF98Zs3JzvvQUO0gAbUK4i/T2W4/TgdHNUlx3A5CHgBkEYLBwofVzfgkzt3H8R2f4o2PJvRipU3imyGfMxojlK6n9wLxElA4pKCuYPfb4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Accessed = _t, Purchased = _t, Paid = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Accessed", Int64.Type}, {"Purchased", Int64.Type}, {"Paid", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each Date.ToText([Date],"MMMM")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Month"}, {{"1-Accessed", each List.Sum([Accessed]), type number}, {"2-Purchased", each List.Sum([Purchased]), type number}, {"3-Paid", each List.Sum([Paid]), type number}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Grouped Rows"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type text}, {"January", Int64.Type}, {"February", Int64.Type}, {"March", Int64.Type}})
in
#"Changed Type2"
For more details, please check the pbix as attached.
Regards,
Frank