Forum Discussion
Categories And Or
Hello, in power BI I try to display the categories of my expenses. But when some expenses correspond to two categories, instead of showing them in each category, it creates a new category.
For example, if I have an expense in the "food" and "leisure" category, I would like it to be counted in the "leisure" category and in the "food" category instead of creating a third category called "leisure & food".
The problem may come from my database (here attached some screen shots)
Thanks in advance
Damien
Hi Caracole ,
In order to provide you with a suitable solution, could you please provide some sample data (with expense and category information) and the final result you want without sensitive info? If there is an expense of $600 that is recorded in the [leisure, food] category, then in the end you would like to calculate it in the food (600/2=300) and leisure (600/2=300) categories respectively? If so, I created a sample file(see attachment) for you and see if it is what you want...
1. Split the category column in Power Query Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZCxDoMwDER/pcocidgpEH6gE1u7IQZEI5UlqZLy/20QcRJRFi/vfD7fMDCsoEKByDhDIX7z4Sbj39Z9PBt54Bg5YOA3a587kZHIbbPXi1+d5pdMcqVlGST33Limw7AZL2Yys460IXNVmPermV+7piUHeRZAkaQ7BOjSZ3gMAIJwq/4VA1D+n12FVFrdnKUHmQpQxf3xCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Expense = _t, Category = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Expense", Int64.Type}, {"Category", type text}}), #"Split Column by Delimiter" = Table.TransformColumns( Source, {"Category", each Text.Split(_,", "), type list}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Expense", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "NExpense", each [Expense] / List.Count( [Category] )), #"Expanded Category" = Table.ExpandListColumn(#"Added Custom", "Category"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Category",{"Expense"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"NExpense", Int64.Type}, {"Date", type date}, {"Category", type text}}) in #"Changed Type2"2. Create a treemap visual with processed data
Best Regards
5 Replies
Caracole , I think you have to create new column using switch or if
Hi thank you for your answer,
I tried to create a new column with SWITCH function but it only solve partially the problem.
For exemple if i write (...,"Loisir, Midi","Loisir",...) the categorie will count "Loisir, Midi" as "Loisir" but not at "Loisir" and "Midi".
Same way if i write (...,"Loisir, Midi","Midi",...) I'll only have "Midi" in my new column.
I've tried to write (...,"Loisir, Midi","Loisir","Loisir, Midi","Midi",...) but it count only the first one ..
Hi Caracole ,
In order to provide you with a suitable solution, could you please provide some sample data (with expense and category information) and the final result you want without sensitive info? If there is an expense of $600 that is recorded in the [leisure, food] category, then in the end you would like to calculate it in the food (600/2=300) and leisure (600/2=300) categories respectively? If so, I created a sample file(see attachment) for you and see if it is what you want...
1. Split the category column in Power Query Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZCxDoMwDER/pcocidgpEH6gE1u7IQZEI5UlqZLy/20QcRJRFi/vfD7fMDCsoEKByDhDIX7z4Sbj39Z9PBt54Bg5YOA3a587kZHIbbPXi1+d5pdMcqVlGST33Limw7AZL2Yys460IXNVmPermV+7piUHeRZAkaQ7BOjSZ3gMAIJwq/4VA1D+n12FVFrdnKUHmQpQxf3xCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Expense = _t, Category = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Expense", Int64.Type}, {"Category", type text}}), #"Split Column by Delimiter" = Table.TransformColumns( Source, {"Category", each Text.Split(_,", "), type list}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Expense", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "NExpense", each [Expense] / List.Count( [Category] )), #"Expanded Category" = Table.ExpandListColumn(#"Added Custom", "Category"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Category",{"Expense"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"NExpense", Int64.Type}, {"Date", type date}, {"Category", type text}}) in #"Changed Type2"2. Create a treemap visual with processed data
Best Regards
Hi Anonymous ,
Thank you very much for this solution and the time you took to write it ! It seems to work well for now 🙂
Sory for the delay I'm doing BI on the side of my work it took me a little time to test everything.Kind regard,
Damien
Hi Caracole ,
Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the soluiton easily if they face the same problem with you. Thank you.
Best Regards
