Forum Discussion
Funnel Chart with filtered months
- 7 years ago
Hi 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
- zeitgeist7 years agoRegular Visitor
Hi, thanks for the answer.
unfortunately I stoped right at the start. It returned error on the Date.toText function:
A sintaxe de '.' está incorreta. (DAX(Date.ToText([Date],"MMMM"))).
Just to be sure, since I am new to powerBi, I entered the Data view, selected my source with the table I sent before, right clicked on it and clicked on the "New Column" item.
Is that correct?
Thanks
- v-frfei-msft7 years agoCommunity Support
Hi zeitgeist,
Now in the data view pane. Please check the steps as the picture as below.
Edit Queries - add custom column - enter the formula.
Regards,
Frank
- zeitgeist7 years agoRegular Visitor
Hi Everyone.
Sorry for the lack of answers but I am away from work these days so I don't have access to the original files. I will get back and try the solution until Friday.
Thanks for the help!