how to make Power query negative (-) for continuous 7 days will automatic show Red words

[Oscarr]

Hi all

If the customer have negative (-) for continuous 7 days, will automatic show Red words beside the customer’s name. No matter on which day, as long as continuous 7 days, and it will auto show Red words beside the customer's name. For those who do not have negative for continuous 7 days will show Green words beside the customer’s name

 

[Chihiro]

Is this PowerQuery question? I ask, since your data structure isn't conducive to PowerQuery and you don't have any query in your sample workbook.

 

[Oscarr]

Is this PowerQuery question? I ask, since your data structure isn't conducive to PowerQuery and you don't have any query in your sample workbook.

@Chihiro

Yes, is power query question....I juat upload example as I want to do this at power query, is this possible ?

 

[Chihiro]

Is your file in actual raw data format? If so, it could be done. But I'd strongly advise against it.

In PQ and DAX, you'd want all calculation to be done in context, i.e. table should be flattened out and avoid mixing in null values where appropriate.

As well, in your sample, there's no aggregation done. In these circumstance, it's better to use regular Excel formula rather than doing calculation in PQ.

What's the actual raw data structure and end result you are after?

 

[Oscarr]

Is your file in actual raw data format? If so, it could be done. But I'd strongly advise against it.

In PQ and DAX, you'd want all calculation to be done in context, i.e. table should be flattened out and avoid mixing in null values where appropriate.

As well, in your sample, there's no aggregation done. In these circumstance, it's better to use regular Excel formula rather than doing calculation in PQ.

What's the actual raw data structure and end result you are after?

@Chihiro

That actual data is don have detail column(red/green)words, and now I have to one by one to check which customer negative is continuous 7das and which is no....so I want do this at power query, you can help this?

 

[Chihiro]

In PQ, you'll need to think like database (SQL) and not as Excel. So most calculation should be done row wise and not across columns.

To make it dynamic, it will require looping logic and is very inefficient method of calculation. Hence, my suggestion on flattening data. (Alternately you'll need to have two separate query and do aggregation in one and merge it back onto original).

Otherwise, this sort of calculation is best done in Excel, using Frequency function.

In C21:
=IF(MAX(FREQUENCY(IF(D21:AH21<0,COLUMN(D21:AH21)),IF(D21:AH21>=0,COLUMN(D21:AH21))))>=7,"Red","Green")

Confirmed as Array (CTRL + SHIFT + ENTER). Copy down.

 

[Chihiro]

Just as academic interest, here's how to do it on PQ.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}, {"6", Int64.Type}, {"7", Int64.Type}, {"8", Int64.Type}, {"9", Int64.Type}, {"10", Int64.Type}, {"11", Int64.Type}, {"12", Int64.Type}, {"13", Int64.Type}, {"14", Int64.Type}, {"15", Int64.Type}, {"16", Int64.Type}, {"17", Int64.Type}, {"18", Int64.Type}, {"19", Int64.Type}, {"20", Int64.Type}, {"21", Int64.Type}, {"22", Int64.Type}, {"23", Int64.Type}, {"24", Int64.Type}, {"25", Int64.Type}, {"26", Int64.Type}, {"27", Int64.Type}, {"28", Int64.Type}, {"29", Int64.Type}, {"30", Int64.Type}, {"31", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Name"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Value] < 0 then "1" else "0" ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each if [Value] < 0 then 1 else 0),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 0, 1),
    #"Added Custom2" = Table.AddColumn(#"Added Index", "Custom.1", each try if #"Added Index"{[Index]-1}[Custom] <> [Custom] then [Index] else null otherwise [Index]),
    #"Filled Down" = Table.FillDown(#"Added Custom2",{"Custom.1"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Name", "Custom", "Custom.1"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Custom] = 1)),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Name"}, {{"MaxNeg", each List.Max([Count]), type number}}),
    #"Added Custom3" = Table.AddColumn(#"Grouped Rows1", "Flag", each if [MaxNeg] >= 7 then "Red" else "Green")
in
    #"Added Custom3"

You then merge this query back into Table1. Using Name as key.
Again, I wouldn't recommend this approach.

 

[Oscarr]

In PQ, you'll need to think like database (SQL) and not as Excel. So most calculation should be done row wise and not across columns.

To make it dynamic, it will require looping logic and is very inefficient method of calculation. Hence, my suggestion on flattening data. (Alternately you'll need to have two separate query and do aggregation in one and merge it back onto original).

Otherwise, this sort of calculation is best done in Excel, using Frequency function.

In C21:
=IF(MAX(FREQUENCY(IF(D21:AH21<0,COLUMN(D21:AH21)),IF(D21:AH21>=0,COLUMN(D21:AH21))))>=7,"Red","Green")

Confirmed as Array (CTRL + SHIFT + ENTER). Copy down.

@Chihiro

I tried both of you give, this is the best way to use...