CODEWITHSUNDEEP
datedynamicconditional-statementspowerquery
Katarzyna
Katarzyna

Reputation: 11

M Power Query - conditionally convert values from a dynamic number of columns by comparing values against two reference columns

I am stuck with a difficulty I am not able to solve by myself.

There is a number of columns created dynamically in the dataset. Their number can vary (from several dozens to over a hundred). They hold data in following date format: 01.10.2021 (day/month/year). In the examples below the columns are called Date1, Date2, Date3.

And there are two reference columns, also containing dates - Start Date and End Date (for a subscription). I want to convert the date values conditionally from date into a 0-1 format, depending on whether condition in the cell is met against the reference dates.

I want to check for each of the date columns, whether the date in a cell is between start date and end date of subscription, or after the start date if there is no end date (in other words, whether the subscription was active or not on a particular day). I want to return 1 if subscription was active or 0 if it was not active on the particular date.

The example table before conversion:

Start Date End Date Date1 Date2 Date3
01.01.2020 01.05.2021 01.03.2020 01.04.2021 01.09.2021
01.05.2020 01.08.2021 01.03.2020 01.04.2021 01.09.2021
01.02.2021 null 01.03.2020 01.04.2021 01.09.2021

Desired outcome after conversion:

Start Date End Date Date1 Date2 Date3
01.01.2020 01.05.2021 1 1 0
01.05.2020 01.08.2021 0 1 0
01.02.2021 null 0 1 1

I have tried multiple solutions, but there must be an error in my syntax, cause I get an expression error: "We cannot apply field access to the type date".

The last solution I tried was:

Transformation = Table.TransformColumns(#"Previous step", List.Transform(FunctionToReturnListOfColumnNames(), each {_, each if [Start Date] <= _ and ([End Date] = null or [End Date] > _) then 1 else 0, type number}))

The function returning the list of column names works properly, the bug is somewhere in the conditional expression.

I tried to convert all the date columns into integers first with Number.From() function, supposing there might have been problems with logical operators applied to dates, but the problem persist even if all columns are converted to numbers, the error is the same, only for the type number: "We cannot apply field access to the type number"

I would very much appreciate if you could point the bug in my syntax.

Upvotes: 1

Views: 405

Answers (1)

horseyride
horseyride

Reputation: 21353

Unpivot ... compare ... repivot

All the steps:

Click select first two columns, right click, unpivot other columns

Add custom column with formula

 if [Start Date] <= [Value] and ([End Date] = null or [End Date] > [Value]) then 1 else 0

right click and remove Value column

click select attribute column

transform ... pivot column ... values column = custom

if desired change format of Start Date and End Date columns to dates

enter image description here

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Start Date", "End Date"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Start Date] <= [Value] and ([End Date] = null or [End Date] > [Value]) then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom", List.Sum)
in #"Pivoted Column"

Upvotes: 1

Related Questions