Adding New Column If Column B Reach The Target

[ARRAM]

Hello,

Well I have this table for an example,

and I want to add new column name (Status) that's show if column B have more or equal 100 then Status = "Complete" else "Not Complete".

And then I change the status "Not Complete" to "Complete" IF any duplicate in name column have 1 Complete value in status column.

So could anyone help me how to make this in power query and i'd really appreciate it.

[ImkeF · Accepted solution]

Hi @ARRAM ,
I would recommend to group your data by Country and then use MAX-aggregation and the All Rows aggregation.
That allows you to add a custom column with a simple formula like if [myNewMaxColumn] > 100 then "Complete" else "Not complete". After that, expand the remaining columns from the "All Rows" table.

Next time, please provide your sample data in a form that can be copied and worked with like described here:
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[jennratten · Accepted solution]

Hello - this is how you can achieve the expected result... I have included the script and screenshots below as well as a sample file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZNtb4IwEMe/CuG1S7YsfoBRh52SjADTLMYXVS7SUFpTcInffr2WuhLdK+4pd7/799jt4s17PIs30A+9+c7j/WwXl9SYZaO0jb082+A2MXaihkFAHzFZR4SdwOVdQUL+K5i7gnxtnIrrMV1dDjb96rLlJw5Vx9ZyuFjxhi15C1HBXOJ1HEYy4xABTIL+A09xlxRk7YIjeVn5LuVgBofM9Ns49FprNnAlo3yc4onLlXFWphlcA9K0wD3URXN5ilLNOgiRF8ZegGZcCLjokLmwCmrWQiA1EpesrgUExFX1UClPXX08TnudESGHmokQ+p5rLKeIRU0jAQc2AU4fAJMShddMtj2E50HwZkjDuJzcxDqQCp8g5Ez8RakuMlOOrWs4Ai/xhZdC/YTq5ngQOZOSw4Q0w+PJ/PG40i8svXTnADJDXTN+aoaJnEsrp4/6t7d0/PB0+xFGsGJr7G0DIAKwdBK8CYjrp0oHWBT1o8DqqXw0s2HRwQSN4L6EnUOw7P4o978=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Category Code" = _t, #"Product Category Name" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category Code", type text}, {"Product Category Name", type text}, {"Sales", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product Category Name"}, {{"SumSales", each List.Sum([Sales]), type nullable number}, {"Data", each _, type table [Category Code=nullable text, Product Category Name=nullable text, Sales=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Status", each if [SumSales] >= 100 then "Complete" else "Not Complete", type text),
    #"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"Sales"}, {"Sales"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"SumSales"})
in
    #"Removed Columns"

[ImkeF · Accepted solution]

Hi @ARRAM ,
I would recommend to group your data by Country and then use MAX-aggregation and the All Rows aggregation.
That allows you to add a custom column with a simple formula like if [myNewMaxColumn] > 100 then "Complete" else "Not complete". After that, expand the remaining columns from the "All Rows" table.

Next time, please provide your sample data in a form that can be copied and worked with like described here:
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...