Power Query Synthax

[sophie63]

I would liketo create a customed column (the orange one) in my table, that tells me the evolution of the status of a project depending on the time version. For instance here, in my Project ID my projects appear as many times as there are different time version saved. But I would like to write a formula telling me in a new colum the evolution, if there is, of the status for each project between the LIVE version and the others. However I have no clue how I could do that and what the synthax is...

Thanks for helping!

[sophie63]

And now I am trying that ...

[MarcelBeug]

It looks like you are aiming for a solution in DAX.

Based on the suggestions from @Sean, you can try and adjust "TRUE" to TRUE() and "FALSE" to FALSE().

If this doesn't help and if a solution in Power Query is also OK with you, I can take a closer look later today.

Just let me know,

[sophie63]

I opted for DAX because I thought I could find a solution with it but I didn't... So if you manage to find a sort of solution it would be great! I tried also by creating few calculated columns but didn't work out. The problem is that there are many version per projects...

Let me know if you find a way.

Thanks a lot for helping!

[MarcelBeug · Accepted solution]

The exact criteria for true or false are still unclear to me, but I gave it a shot.

I created an Excel fle with the data.

In Power BI I created the Power Query code below. It includes explanations (the lines starting with //).

You can copy the code: in Power BI choose "Get Data" - Blank Query - Advanced Editor and replace the default code with the code below. Adjusst the data source to yours, choose "Done"  and then - on the Home tab - choose Close & Load.

I also created this video that takes you through the query steps. It is not a live recording of the creation of the query, but a walkthrough after I created the query.

Possibly some detailed adjustment is still required, but this should be close to the final result.

let
    // Next 3 steps are created when importing the data from Excel
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Power Query Syntax - Project status changes.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"PROJECT ID", Int64.Type}, {"STATUS", type text}, {"DATE_VERSION", type text}}),

    // Add Index for the original sort so the data can be sort back at the end
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Original Sort", 1, 1),

    // Add column with real date from [DATE VERSION]. "nl-NL" is used so format DD-MM-YYYY is recognized. 31-12-9999 for "LIVE", so it will sort at the end
    #"Added Custom" = Table.AddColumn(#"Added Index", "Version_Date", each if [DATE_VERSION] = "LIVE" then #date(9999,12,31) else Date.From(Text.Replace([DATE_VERSION],"_","-"),"nl-NL"), type date),

    // Sort on Project and date
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"PROJECT ID", Order.Ascending}, {"Version_Date", Order.Ascending}}),

    // Add 2 indices so the table can be merged with itself, such that the project and status from the previous row will be on the current row
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Index"},#"Added Index2",{"Index.1"},"Previous",JoinKind.LeftOuter),

    // After the merge, we need the previous project ID and the previous status
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"PROJECT ID", "STATUS"}, {"Previous.PROJECT ID", "Previous.STATUS"}),

    // Add a column with true if: the current version = "LIVE" and the Project ID = Previous Project ID and the Status <> Previous Status, else false 
    #"Added Custom1" = Table.AddColumn(#"Expanded Previous", "LIVE Status <> Previous Status?", each [DATE_VERSION] = "LIVE" and [PROJECT ID] = [Previous.PROJECT ID] and [STATUS] <> [Previous.STATUS], type logical),

    // Sort back to the original sort
    #"Sorted Rows1" = Table.Sort(#"Added Custom1",{{"Original Sort", Order.Ascending}}),

    // Remove columns that are no longer required
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Original Sort", "Version_Date", "Index", "Index.1", "Previous.PROJECT ID", "Previous.STATUS"})
in
    #"Removed Columns"

[sophie63]

Thank you @MarcelBeug  , that's really helpful!

Problem solved 🙂

Have a nice day!

[sophie63]

For instance I am trying that, but it's definitely not the right way. First, that does not work. Second, that does does take into account the variation on one particular project between two dates/versions.

[sophie63]

Thanks everyone ! 🙂 @Sean @Anonymous @MarcelBeug

It helps but maybe I wasn't super clear in my initial request. It's a bit more complicated: I have multiple date version (not only december but all the months of many years), so I want to get a TRUE when for instance there is a variation of status for the same project between two versions (I choose a comparison betwwen December 2016 and today=LIVE).  Something like :

IF (Project_ID=123 & Version=LIVE & Status=CLOSED) AND (Project_ID=123 & Version=01_12_2016 & Status=OPEN) THEN TRUE

ELSE FALSE

But I don't really know how to manage these multiple AND...

[Anonymous]

Hi @sophie63

I'm not sure if I got your requirements right.

Here is a code for an added column. The code checks, if [STATUS] is closed or not.

You may change the code to your needs.

let
    YOUR_LAST_STEP = THE_LAST_LINE_OF_CODE_YOU_WROTE

    #"Added Custom" = Table.AddColumn(YOUR_LAST_STEP, "CLOSED_2017", each if [STATUS] ="CLOSED" then "TRUE" else "FALSE")
in
    #"Added Custom"

[Sean]

The Query Editor has an even easier Conditional Column option... under the Add Column tab

So the above will generate your M code

However if you want this done with DAX

CLOSED_2017 DAX = IF ( 'Table'[STATUS] = "CLOSED", "TRUE", "FALSE")

You can also use the functions TRUE( ) an FALSE( ) above... don't put those in quotes

CLOSED_2017 DAX = IF ( 'Table'[STATUS] = "CLOSED", TRUE () , FALSE () )

Hope thsi helps!

[Anonymous]

One question, three solutions... 😉

That's Power Query!

[MarcelBeug]

Gentlemen @Anonymous and @Sean

1. It's not only Power Query, also DAX.
2. By using the "Add Conditional Column" functionality, you get "TRUE"/"FALSE", not the logicals true/false (unless parameters are defined for TRUE and FALSE and selected in the values fields in the screen for adding a conditional column).

[Anonymous]

@MarcelBeugand @Sean

1. you are right, Marcel

2. the solution has to depend on the customer's requirements (boolean or text). We don't know it at the moment.

She knows it and she will it to us 😉

[Sean]

@MarcelBeug

My second DAX Measure returns Boolean

[MarcelBeug]

The requirements are not clear to me either, but anyhow I would expect the result should be a logical true or false, not text.

So if "CLOSED" means true and otherwise false, I would adjust the code from @Anonymous to:  

let
    YOUR_LAST_STEP = THE_LAST_LINE_OF_CODE_YOU_WROTE

    #"Added Custom" = Table.AddColumn(YOUR_LAST_STEP, "CLOSED_2017", each [STATUS] ="CLOSED")
in
    #"Added Custom"

But I guess the requirements will be more complicated as we get further explanation and examples from @sophie63