Conditional statements i.e countif

[Circuscazz]

Hi 

I hope you can help?

I have a table like that shown below in Power BI. I'm trying to get a count of apples picked, appled delivered, bananas picked and bananas delivered by Date. 

Apples Picked	Apples delivered	Bananas Picked	Bananas delivered
01/01/2024	02/01/2024	20/12/2023	24/12/2023
30/12/2023	31/12/2023	21/12/2023	24/12/2023
30/12/2023	02/01/2024	01/01/2024	01/01/2024
28/12/2023	02/01/2024	01/01/2024	02/01/2024

Therefore I would like to build the table below. Can this be done in power query? I would like it automatically based on today's date and descending for last 365 days? 

	Count of Apples Picked	Count of Apples delivered	Count of Bananas Picked	Count of Bananas Picked
02/01/2024	0	3	0	1
01/01/2024	1	0	2	1
31/12/2023	0	1	0	0
30/12/2023	1	0	0	0
29/12/2023	0	0	0	0
28/12/2023	1	0	0	0
27/12/2023	0	0	0	0
26/12/2023	0	0	0	0
25/12/2023	0	0	0	2
24/12/2023	0	0	2	0

[jennratten]

Hello - this is how you can do it...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJR0gEyjeBMEAvEMYZyTCCcWJ1oENcYWc7YEFmXITG6jJAtxWRC1RtZ4FcPF42NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Apples Picked" = _t, #"Apples delivered" = _t, #"Bananas Picked" = _t, #"Bananas delivered" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Apples Picked", type text}, {"Apples delivered", type text}, {"Bananas Picked", type text}, {"Bananas delivered", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Value", "Date"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Attribute]), "Attribute", "Value", List.Count),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Date.IsInPreviousNYears([Date], 1)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Descending}})
in
    #"Sorted Rows"

[Circuscazz]

Hi

Thanks, did you create a new data table, or can i amend to the original table? 

[jennratten]

I used your data but changed the dates in the source step to M/d/yyyy to work with my locale.  To apply this solution to your data table, use everything except for the source step, like so (just change #"Changed Type" to the name of your last step):

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Value", "Date"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Attribute]), "Attribute", "Value", List.Count),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Date.IsInPreviousNYears([Date], 1)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Descending}})
in
    #"Sorted Rows"

[Circuscazz]

Thanks but i've stumbled on an issue, the table above are appended to a data table with lots of columns. However when i attempt to pivot the required column is doesnt work due to the additional columns and repeats the dates. Can I create a linked data table to focus solely on the pivioted columns? Let me know if i need to show an example

[jennratten]

Hi @Circuscazz - Yes, you can create a linked reference, limit the columns omit those that are not needed and then perform the pivot.