Dynamically pull dated column based on date in another query

[Susan513]

I am looking to pull a specific column conditionally against a date - which is dated i.e. Jan-24, Feb-24, Mar-24 etc. for reporting and varies each month. As some of the rows are blank and some are not- transposing to get last relevant row did not work well. 

I think the best route is to tell the system the date I want, which I created a seperate query to show the date. So the new custom column takes the data from the column named the same as my report date query date and creates the new Report Data column. 

Any ideas, am I overthinking this? I was trying to think out just filtering for the month on reports, but I am not certain it would work well as the data is in columns. I also have document names, goal status and other data in the same table.

Here is sample data view. I want to add the Report Data Column in this example, where Feb-2024 data is put into the new column, based on my ReportDate Query, where I have Feb-2024. (if reportdate query date = any column, then that column)

Thank you for any insight!

Site	Document	Jan-24	Feb-24	Mar-24	ReportData		Report Date Query
A	Doc 1	Yes	Yes	Yes	Yes		Feb-24
A	Doc 2	No	In Progress	Yes	In Progress
A	Doc 2	No	No	In Progress	No
B	Doc 1	No	In Progress	Yes	In Progress
B	Doc 2	No	No	In Progress	No
B	Doc 3	No	In Progress	Yes	In Progress
C	Doc 1	No	No	In Progress	No
C	Doc 2	Yes	Yes	Yes	Yes
C	Doc 3	No	No	In Progress	No
D	Doc 1	Yes	Yes	Yes	Yes
D	Doc 2	No	In Progress	Yes	In Progress
D	Doc 3	Yes	Yes	Yes	Yes
E	Doc 1	No	In Progress	Yes	In Progress
E	Doc 2	No	No	In Progress	No
E	Doc 3	No	In Progress	Yes	In Progress
F	Doc 1	No	No	In Progress	No
F	Doc 2	Yes	Yes	Yes	Yes
F	Doc 3	No	No	In Progress	No
G	Doc 1	Yes	Yes	Yes	Yes
G	Doc 2	No	In Progress	Yes	In Progress
G	Doc 3	Yes	Yes	Yes	Yes
I	Doc 1
I	Doc 2
I	Doc 3
P	Doc 1
P	Doc 2
P	Doc 3
Q	Doc 1
Q	Doc 2
Q	Doc 3

[FarhanJeelani]

It sounds like you want to dynamically select a column based on the date in your `Report Date Query` so that the `Report Data` column reflects the data from the month corresponding to that report date. Here’s a way to accomplish this in Power Query (M) within Power BI:

Steps in Power Query

1. Load your main data table and the `Report Date Query` table into Power Query.

2. Extract the Report Date:
- In the `Report Date Query` table, ensure you have a single row with the date that corresponds to the column you want to pull. For example, if your report date is `Feb-24`, this should be set as a single value in that query.
- Extract this value as a parameter or as a single variable by renaming the column to `Report Date`.

3. Create a Dynamic Column Selection:
- In the main table, add a new column called `Report Data` to dynamically pull values from the column that matches the `Report Date`.

4. Add Custom Column with Conditional Logic:
- Go to the main table in Power Query, and add a Custom Column with the following formula:

M query
= Table.AddColumn(MainTable, "ReportData", each Record.Field(_, Text.From(ReportDateQuery[Report Date]{0})))

- Here’s what this does:
- `Record.Field(_, Text.From(ReportDateQuery[Report Date]{0}))`: `Record.Field` extracts the field in the current row that matches the name of the `Report Date` (for example, "Feb-24").
- `ReportDateQuery[Report Date]{0}` extracts the first row’s value from the `Report Date Query`, which should match one of the columns in the main table.
- This dynamically pulls the data from the column in the main table corresponding to the specified `Report Date`.

5. Close and Apply:
- Once you've added the custom column, click Close & Apply to load the changes into Power BI.

Explanation

This approach allows you to select the data from a specific column dynamically, based on the `Report Date` value in your `Report Date Query`. If `Report Date Query` is updated to a different month, the `Report Data` column will automatically update to reflect data from that corresponding column.

Let me know if you need further customization or adjustments for your specific setup!

[Anonymous]

Hi @Susan513 ,
Based on your description, you want to create a new ReportData column based on the Report Date Query column, you can try the following code

let
    Source = ...,
    ReportDate = List.First(List.RemoveNulls(Source[Report Date Query])),
    AddReportData = Table.AddColumn(Source, "ReportData", each Record.Field(_, ReportDate))
in
    AddReportData

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Vijay_A_Verma]

You can insert this step

= Table.DuplicateColumn(Source, #"Report Date Query", "ReportData")

 or more robustly

= if Table.HasColumns(Source, #"Report Date Query") then Table.DuplicateColumn(Source, #"Report Date Query", "ReportData") else Source