Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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-...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
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"
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-...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries