Create new column using condition

[AMD2183]

Hi and good day members, 

Iam fairly new to power bi and still learning by practicing on my own. Can anybody help me on how i can create "Column D" and "Column E" using the condition in E4 and F4 respectively. I can get the idea in excel but on power query seems a lost for me. Iam expecting result as per image below.

Expected Result

[Anonymous]

Hi @AMD2183 ，
First of all, it is clear that there is no concept of merging cells in power bi, so there should be a corresponding value for each piece of data. You can refer to the following m code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kksKMkvcFTSUQLhgMTiYqVYHWRhJyB2S8zMQRN2xlTtBDUETbUTdkOcsBvijN0lzlBDsAgjDIkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, ColumnB = _t, ColumnC = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Column D", each if ([ColumnB] = "A" or [ColumnB] = "B") and [ColumnC] = "Pass" then "Pass" else if [ColumnB] = "C" and [ColumnC] = "Pass" then "Pass" else "Fail"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Column D.1", each if [ColumnC] = "Pass" then "Pass" else "Fail
"),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Column A"}, {{"AllPass", each List.AllTrue(List.Transform([Column D], each _ = "Pass")), type logical}}),
    #"Merged Tables" = Table.NestedJoin(#"Added Custom1", {"Column A"}, #"Grouped Rows", {"Column A"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Tables", "Grouped Rows", {"AllPass"}),
    #"Added NewColumn" = Table.AddColumn(#"Expanded Table", "Column E", each if [AllPass] then "Pass" else "Fail"),
    #"Removed Columns" = Table.RemoveColumns(#"Added NewColumn",{"AllPass"})
in
    #"Removed Columns"

Here for Column D you use the logic you provided. For Column D.1 is the simplified logic.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Omid_Motamedise]

In power query there is no merge cell, so after loading data into power query, use fill down to replace any null value with its previous value. Then use add new column and use if as almost the same as excel with the below structure

If ....... then ........else ........

[uzuntasgokberk · Accepted solution]

Hello @AMD2183,

You can use below the code of Power Query.

Add a new custom column example "E".
if ([C] = "A" or [C] = "B") and [D] = "PASS" then "PASS"
else if [C] = "C" and [D] = "PASS" then "PASS"
else "FAIL"

And again add new custom column.
if ([E] = "PASS" or [E] = "PASS") and [E] = "PASS" then "PASS"
else "FAIL"

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.