If all Values contain Text based on another range, then flag as TRUE

[fdiddy]

Hi All,
Stuck on the below in Power Query for Excel

Want to create a new column, if all values in Sold/Unsold equals "Unsold" for the particular product(in this case water slide), then Flag as 1

In this example below it should be false because all values are not "Unsold"

Country	product	Sold/Unsold
Belgium	water slide	Unsold
Denmark	water slide	Unsold
India	water slide	Sold
Finland	water slide	Sold
Netherlands	water slide	Sold
Norway	water slide	Unsold
Sweden	water slide	Sold

Desired Outcome

Country	product	Sold/Unsold
Belgium	water slide	Unsold	0​
Denmark	water slide	Unsold	0​
India	water slide	Sold	0​
Finland	water slide	Sold	0​
Netherlands	water slide	Sold	0​
Norway	water slide	Unsold	0​
Sweden	water slide	Sold	0​

 

[Kerryx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"product", type text}, {"Sold/Unsold", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Check", each if [#"Sold/Unsold"] = "Unsold" then 1 else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Final", each if(List.Sum(#"Added Custom"[Check]))>0 then 1 else 0)
in
    #"Added Custom1"

You can reverse the 0 / 1 if needed

 

[fdiddy]

Thanks for the input but it doesnt seem to be working for me

 

[Kerryx]

Yes because you have different products, try this may not be the neatest but should work

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"product", type text}, {"Sold/Unsold", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Check", each if [#"Sold/Unsold"] = "Unsold" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"product"}, {{"Final2", each List.Sum([Check]), type number}, {"Count2", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"product"}, Table1, {"product"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Country", "product", "Sold/Unsold", "Check"}, {"Table1.Country", "Table1.product", "Table1.Sold/Unsold", "Table1.Check"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Table1", "Final", each if([Final2]=[Count2]) then 0 else 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"product", "Final2", "Count2"})
in
    #"Removed Columns"

 

[smozgur]

If all are Unsold then 1 otherwise 0.
Perhaps the following:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"product"}, {{"Count", each _}}),
    Sold = Table.AddColumn(Group, "UnSold", each if List.Contains([Count][#"Sold/Unsold"], "Sold", Comparer.Ordinal) then 0 else 1),
    Result = Table.ExpandTableColumn(Table.NestedJoin(Source, "product", Sold, "product", "Unsold"), "Unsold", {"UnSold"})
in
    Result

 

[Kerryx]

Perfect smozger was just looking at tidying it up and explaining about the group option , thanks for stepping in saved me .....

 

[smozgur]

Perfect smozger was just looking at tidying it up and explaining about the group option , thanks for stepping in saved me .....

 

[fdiddy]

Thanks so much Guys !! I have yet to test it but it looks good !