Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
filipebodas
Helper I
Helper I

Conditional Column

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:

IDColumn_1Column_2
1AB
1AB
1BB
2AA
2AA
3AB
3BB
3AB


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!

 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @filipebodas ,

 

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:

BA_Pete_0-1644577834250.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Super User
Super User

Hi @filipebodas ,

 

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:

BA_Pete_0-1644577834250.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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 

Hi @filipebodas ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors