Categories And Or

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