What is the best way to create a column based on conditions of other columns?

[datamonger13]

I want to create a column called SessionType (Gait Assessment or Daily Note). The problem is that STRIDE WALKED and STRIDE DISTANCE are collected during BOTH sessions, so its assignment to the column will be conditional. 

A row should be designated as a Gait Assessment if:

1. HealthFactorType = STRIDE ELIGIBLE YES
2. HealthFactorType = STRIDE WALKED AND it has the same date as STRIDE ELIGIBLE YES
3. HealthFactorType = STRIDE DISTANCE AND it has the same date as STRIDE ELIGIBLE YES

A row should be designated as a Daily Note if:

1. HealthFactorType = STRIDE SESSION COMPLETED
2. HealthFactorType = STRIDE WALKED AND it has the same date as STRIDE SESSION COMPLETED
3. HealthFactorType = STRIDE DISTANCE AND it has the same date as STRIDE SESSION COMPLETED 

What is the best way to write the new column for this case?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[datamonger13]

Here is a table of sample data:

HealthFactorDate	PatientName	HealthFactorType	Stride Eligibility	Stride Session	Walking Time	Distance Walked	Feeling After	Adverse Effect
3/30/2025	Smith, Joe	STRIDE ELIGIBLE YES	YES	null	null	null	null	null
3/30/2025	Smith, Joe	STRIDE WALKED	null	null	6-10 MINUTES	null	null	null
3/30/2025	Smith, Joe	STRIDE DISTANCE	null	null	null	251-500 FEET	null	null
3/31/2025	Smith, Joe	STRIDE SESSION COMPLETED	null	YES	null	null	null	null
3/31/2025	Smith, Joe	STRIDE WALKED	null	null	6-10 MINUTES	null	null	null
3/31/2025	Smith, Joe	STRIDE DISTANCE	null	null	null	251-500 FEET	null	null
3/31/2025	Smith, Joe	STRIDE FEEL AFTER	null	null	null	null	BETTER	null
3/31/2025	Smith, Joe	STRIDE ADVERSE EFFECTS	null	null	null	null	null	NONE

And here is what I want to end up with:

HealthFactorDate	PatientName	SessionType	Stride Eligibility	Stride Session	Walking Time	Distance Walked	Feel After	Adverse Effect
3/30/2025	Smith, Joe	Gait	YES	null	6-10 MINUTES	251-500 FEET	null	null
3/31/2025	Smith, Joe	Daily walk	null	YES	6-10 MINUTES	251-500 FEET	BETTER	NONE

[jgeddes · Accepted solution]

You can group by PatientName and HealthFactorDate choosing all rows as the aggregate.
From there use Table.FillUp() to fill all the null values with values from rows below. Table.FirstN() to take the first row. Expand the table row and then write a simple if then else conditional column.
Sample code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZLBCsIwDIZfpfTcsa5j3rs1lWrXyRoVGTsKClMv+v5OvAx0W0G8JCQkX5ufv2loGqc8FlxklFF/Od9PjKxux1eBtVFAwJqlyS2QA/i++47XR9dNppbNk/fSrkF97C+ihJPSuC2OPRUCV8ajdAWM/VJkSZRxTjQAfscnU3gP3pvKkaIqNxZweMacQiH4X6WZhP9Zmn7NEqkR6hmj5ICDoRC0VDuofe9JraHAICMy6ioHtG2f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [HealthFactorDate = _t, PatientName = _t, HealthFactorType = _t, #"Stride Eligibility" = _t, #"Stride Session" = _t, #"Walking Time" = _t, #"Distance Walked" = _t, #"Feeling After" = _t, #"Adverse Effect" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"HealthFactorDate", type date}, {"PatientName", type text}, {"HealthFactorType", type text}, {"Stride Eligibility", type text}, {"Stride Session", type text}, {"Walking Time", type text}, {"Distance Walked", type text}, {"Feeling After", type text}, {"Adverse Effect", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","null",null,Replacer.ReplaceValue,{"Stride Eligibility", "Stride Session", "Walking Time", "Distance Walked", "Feeling After", "Adverse Effect"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"HealthFactorDate", "PatientName"}, {{"AllRows", each Table.RemoveColumns(_, {"HealthFactorDate", "PatientName", "HealthFactorType"}), type table [HealthFactorDate=nullable date, PatientName=nullable text, HealthFactorType=nullable text, Stride Eligibility=nullable text, Stride Session=nullable text, Walking Time=nullable text, Distance Walked=nullable text, Feeling After=nullable text, Adverse Effect=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.FirstN(Table.FillUp(_, Table.ColumnNames(_)), 1), type table [#"Stride Eligibility"=text, #"Stride Session"=text, #"Walking Time"=text, #"Distance Walked"=text, #"Feel After"= text, #"Adverse Effect"=text]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(Custom1, "AllRows", {"Stride Eligibility", "Stride Session", "Walking Time", "Distance Walked", "Feeling After", "Adverse Effect"}, {"Stride Eligibility", "Stride Session", "Walking Time", "Distance Walked", "Feeling After", "Adverse Effect"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllRows", "SessionType", each if [Stride Eligibility] = "YES" then "Gait" else if [Stride Session] = "YES" then "Daily walk" else null, type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"HealthFactorDate", "PatientName", "SessionType", "Stride Eligibility", "Stride Session", "Walking Time", "Distance Walked", "Feeling After", "Adverse Effect"})
in
    #"Reordered Columns"

[datamonger13]

This is EXACTLY what I was looking for, thank you!!!!! Table.FillUp was the missing link for me - I'm so grateful for your help!!!