Conditionally Populating Values in Multiple Columns with Variable Headers

[rprice768]

Hello all!

I'm working on a query that contains contract start and end dates, as well as columns based on week ending dates, for a number of individuals.  The full dataset is much more involved and has many other columns but the key columns for this issue are shown below with some example data.  The "WE" columns start blank, currently.

Example:

Name	Start Date	End Date	WE 9/14/2024	WE 9/21/2024	WE 9/28/2024
Person 1	9/1/2024	10/26/2024
Person 2	9/15/2024	12/21/2024
Person 3	10/14/2024	1/11/2025
Person 4	7/14/2024	9/7/2024
Person 5	7/22/2024	9/14/2024

Since the resulting report will be "refreshed" periodically and the weeks are added cumulatively, the "week ending" columns to be added by the query will vary.  For each of these "week ending" columns, I need the query to populate "N/A" if that week fell outside each individual's contract dates.

Ideal End Result:

Name	Start Date	End Date	WE 9/14/2024	WE 9/21/2024	WE 9/28/2024
Person 1	9/1/2024	10/26/2024
Person 2	9/15/2024	12/21/2024	N/A
Person 3	10/14/2024	1/11/2025	N/A	N/A	N/A
Person 4	7/14/2024	9/7/2024	N/A	N/A	N/A
Person 5	7/22/2024	9/14/2024		N/A	N/A

Since the column names (and weeks they reference) are variable I created a table that populates all the column names and dates to be added to the report.  So far I have not been able to find a way to populate the "N/A"s based on that table; the column names I have working, it seems.

I did attempt to use Table.ReplaceValue for each of the columns but could not figure out how to designate a different calculation for "new" value for each column.  (I'll preface this with an apology for any weird code - I'm fumbling my way through this report the best that I can.)  I joined my table of new columns to the main table using an arbitrary "Anchor" column, used FillUp to get the dates populated in each newly added column, and then tried to ReplaceValue my way to a solution but could not get it to recognize more than one date in the "oldValue" section.

#"Merged New WE Date Columns" = Table.NestedJoin(#"Replaced Value1", {"Name"}, #"New WE Date Columns", {"Anchor"}, "New WE Date Columns", JoinKind.FullOuter),
#"Expanded New WE Date Columns" = Table.ExpandTableColumn(#"Merged New WE Date Columns", "New WE Date Columns",List.RemoveItems(Table.ColumnNames(#"Merged New WE Date Columns" [New WE Date Columns]{0}), {"Anchor"})),
#"Filled Up" = Table.FillUp(#"Expanded New WE Date Columns",List.RemoveItems(Table.ColumnNames(#"New WE Date Columns"),{"Anchor"})),
Custom3 = Table.ReplaceValue(#"Filled Up", {#"New WE Dates"}, each if [Start Date] > _ or [End Date] < _ then "N/A" else null, Replacer.ReplaceValue, List.Select(Table.ColumnNames(#"Filled Up"), each List.Contains(#"New WE Date Columns (List)",_))),

"New WE Date Columns" is the table with the new headers.

"New WE Dates" is a list of only the week ending dates based on the previous table.

"New WE Date Columns (List)" is a list of the headers on the previous table.  This was possibly unnecessary but it made sense in my head.

Does anyone have any suggestions on how to populate the WE Date columns while complying with the need for variable headers and conditions?  Any and all help is greatly appreciated, and I am happy to provide additional information if needed.

Thank you!

[lbendlin · Accepted solution]

One of the first things you will learn when working with Power BI that you need to bring your data into usable format. The term is "unpivoting.

From 

To

Then you do some cleanup and type casting

And then you can compute the value

There is no need to re-pivot the data here, this can be done for free in the matrix visual.

[rprice768]

Brilliant!  It took a little extra fiddling to get the right columns to unpivot but this worked flawlessly.  Thank you so, so much for your assistance.  I've figured out a decent amount with Power Query but I know I'm missing a good bit of core knowledge so this was a real struggle.  I appreciate you!