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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Susan513
Helper I
Helper I

Dynamically pull dated column based on date in another query

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!

 

SiteDocumentJan-24Feb-24Mar-24ReportData Report Date Query
ADoc 1YesYesYesYes Feb-24
ADoc 2NoIn ProgressYesIn Progress  
ADoc 2NoNoIn ProgressNo  
BDoc 1NoIn ProgressYesIn Progress  
BDoc 2NoNoIn ProgressNo  
BDoc 3NoIn ProgressYesIn Progress  
CDoc 1NoNoIn ProgressNo  
CDoc 2YesYesYesYes  
CDoc 3NoNoIn ProgressNo  
DDoc 1YesYesYesYes  
DDoc 2NoIn ProgressYesIn Progress  
DDoc 3YesYesYesYes  
EDoc 1NoIn ProgressYesIn Progress  
EDoc 2NoNoIn ProgressNo  
EDoc 3NoIn ProgressYesIn Progress  
FDoc 1NoNoIn ProgressNo  
FDoc 2YesYesYesYes  
FDoc 3NoNoIn ProgressNo  
GDoc 1YesYesYesYes  
GDoc 2NoIn ProgressYesIn Progress  
GDoc 3YesYesYesYes  
IDoc 1      
IDoc 2      
IDoc 3      
PDoc 1      
PDoc 2      
PDoc 3      
QDoc 1      
QDoc 2      
QDoc 3      

 

3 REPLIES 3
FarhanJeelani
Super User
Super User

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
Not applicable

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

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

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.