Conditional Column

[Anonymous]

Hello everyone,

I'm working on a table in Power Query and I would like to create a column (Column_2) based on two other columns (ID & Column_1). The data is the following:

ID	Column_1	Column_2
1	A	B
1	A	B
1	B	B
2	A	A
2	A	A
3	A	B
3	B	B
3	A	B

For every rowns related to an ID, if at least one value in Column_1 has a "B" than column 2 is B for every row of that ID. Otherwise is "A".

Can I do this in m languague? Or should I create anothter table in my model and create a relationship 1:* by ID?

Edit: or can this be done in DAX with a calculated column?

Thanks!

[Anonymous]

hi 

create a calculated 

Column 2 =

IF (

FIND (

"B",

CONCATENATEX (

CALCULATETABLE (

sheet3,

ALLEXCEPT (

sheet3,

sheet3[ID]

)

),

sheet3[Column_1]

),

,

-1

) = -1,

"A",

"B"

)

if this post helps , accept it as a solution

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Paste this over the default code in a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitVBZzmBWUZwMQTLGIXlhCwWCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Column_1 = _t]),
    groupID = Table.Group(Source, {"ID"}, {{"data", each _, type table [ID=nullable number, Column_1=nullable text, Column_2=nullable text]}}),
    addBCheck = Table.AddColumn(groupID, "bCheck", each if List.Contains([data][Column_1], "B") then "B" else "A"),
    expandDataCols = Table.ExpandTableColumn(addBCheck, "data", {"Column_1"}, {"Column_1"})
in
    expandDataCols

Summary:

1) Group table on [ID] and retain AllRows as nested tables.

2) Add new column checking each nested table [Column_1] for "B".

3) Expand nested tables back out.

Output:

Pete

[Anonymous]

BA_Pete this worked thank you! How can I change the table from which I call the data. The table I put on the comment is an encripted example from a table that I'm using 

[BA_Pete]

Hi @Anonymous ,

Just create a query with your actual source data then follow the steps that I listed.

1) Select [ID] column, go to Home tab > Group By, call aggregated column "data" and select All Rows as aggregation operator.

2) Add a new custom column to the grouped table, use the following as the calculation, obviously changing "[Column_1], "A", and "B" to whatever the real values are:

if List.Contains([data][Column_1], "B") then "B" else "A"

3) Click the button in the [data] column header that looks like two arrows pointing away from each other. Select the columns that you want to bring back from your original table.

Pete