Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
Bonjour , Hi!
I am looking into creating a dataflow. I already worked on the final table I wanted to have in DAX, but I have a hard time translating the info in PowerQuery M. I am a newbee in PowerQuery and I keep getting errors in everything I tried 😞
I want to add the new column Niv Stage (pour absence) to my source from one table comparing dates from the two tables and a common ID ...
Could someone help me translate the following DAX program in M?
Hi @NathalieM ,
Based on your description, it seems you're trying to add a new column to your source table by comparing dates and a common ID across two tables. In PowerQuery M, this typically involves merging tables based on your common ID and then applying conditional logic to populate your new column.
First, you'll need to merge your _STAGES table with your 'Niveau Stage par année acad' table based on the Résident_NoRésidant column. In PowerQuery, this is done using the Merge Queries feature.
After merging, you can add a custom column to implement your logic. This involves using the if statement in M to compare the dates and then apply your conditions to populate the "Niv Stage (pour absence)" column.
Here's a basic template to guide you:
let
// Merge your tables on the common ID
MergedTable = Table.NestedJoin(_STAGES,{"Résident_NoRésidant"},'Niveau Stage par année acad',{"Résident_NoRésidant"},"NewColumn",JoinKind.LeftOuter),
// Expand the table to include necessary columns from the second table
ExpandedTable = Table.ExpandTableColumn(MergedTable, "NewColumn", {"Date fin dernier stage", "Date premier Stage", "NiveauStage"}, {"EndDate", "StartDate", "Level"}),
// Add a custom column with your logic
CustomColumn = Table.AddColumn(ExpandedTable, "Niv Stage (pour absence)", each if [NiveauStage] <> 0 then [NiveauStage] else if [DateDébutStage] <= [EndDate] and [DateDébutStage] >= [StartDate] then [Level] else null)
in
CustomColumn
Please note, this is a simplified example and might need adjustments based on the exact structure of your tables and the full logic you want to implement. If this doesn't help, please provide some sample data and your expected results, thank you!
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Bonjour Dino Tao,
Unfortunaltely, this won't work because the students have several lines with the same ID on the two tables. One include all stages (can be up to 50 occurences, all levels including absence) , the other one groups beginning and end dates by level (1 to 😎 for each student years. The goal is to know within which levels they were during there absence...(the starting date of the absence has to be within the start and end of the level date for this student)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
13 | |
7 | |
6 | |
6 |