Create a new column in Power BI power query

[Anonymous]

Hi all,

Presently I have a calculated column in one of my tables.

I did did via the 'normal way' in Power BI: Modeling/ Calculations/ New Column.

Now I want to add a column with the same calculation in power query.

Below is the formula, which I want to use in Power query.

Is this possible and if so, can somebody give me some guidance (as this part of Power BI is new to me🤔)?!

Thanks!, John

most recent = 
var thisContainer = 'Kramer_srs_ope_estimate'[container_nr]
var lastDateByContainerNr = 
    CALCULATE(
        MAX(Kramer_srs_ope_estimate[order_dat])
        ,FILTER(
            ALL('Kramer_srs_ope_estimate')
            ,Kramer_srs_ope_estimate[container_nr] = thisContainer
        )
    )
return
IF('Kramer_srs_ope_estimate'[order_dat] = lastDateByContainerNr, "yes", "no")

[edhans · Accepted solution]

Hi @Anonymous - I am not sure I would recommend this. This may not perform well over large (hundreds of thousands) records, but is workable. See the formula in the Max Date step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTLUN9Q3MjAyUIrViVZKAgoYIQskAwWMkQVAWkzRtZihazGHC8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Container Number" = _t, #"Order Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Date", type date}}),
    #"Max Date" = 
        Table.AddColumn(
            #"Changed Type",
            "Max Date",
            each 
                let
                    varCurrentContainer = [Container Number]
                in
            Table.Max(
                Table.SelectRows(
                    #"Changed Type",
                    each [Container Number] = varCurrentContainer
                ),
                "Order Date"
            )[Order Date]
        )
in
    #"Max Date"

Based on my fake data in the first two columns, it returns the max date for each container.

Power Query isn't the best place to do scans of tables. 
Ideally, this would be in a measure, not a calculated column, but I don't know your requirements. Reporting the date in a visual can be done by a measure. If you need the date in a slicer or report filter, then it must go in a column.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

If I've answered your question, please mark as the solution. If not, can you provide some sample data with expected output?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I think @edhans's solution is working perfectly. I create a sample using "Group by" to get the max date. You also could have a try.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA31DcyMDJQ0lFyBGJDI6VYHbCoBbKwsQFY2ETfCCbqBFIMEzU0RjHDDCZsjixsCjPZANkMY7iwKbKwpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Max", each List.Max([Date]), type nullable date}, {"All", each _, type table [Date=nullable date, ID=nullable text, Amount=nullable number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Date", "Amount"}, {"All.Date", "All.Amount"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each if [Max] = [All.Date] then "yes" else "no")
in
    #"Added Custom"

[Anonymous]

good day.,

You are really an expert, thanks so much!

I maneged to solve the issue, together with the info of the other advisor.

Thanks again!

John

[edhans · Accepted solution]

Hi @Anonymous - I am not sure I would recommend this. This may not perform well over large (hundreds of thousands) records, but is workable. See the formula in the Max Date step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTLUN9Q3MjAyUIrViVZKAgoYIQskAwWMkQVAWkzRtZihazGHC8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Container Number" = _t, #"Order Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Date", type date}}),
    #"Max Date" = 
        Table.AddColumn(
            #"Changed Type",
            "Max Date",
            each 
                let
                    varCurrentContainer = [Container Number]
                in
            Table.Max(
                Table.SelectRows(
                    #"Changed Type",
                    each [Container Number] = varCurrentContainer
                ),
                "Order Date"
            )[Order Date]
        )
in
    #"Max Date"

Based on my fake data in the first two columns, it returns the max date for each container.

Power Query isn't the best place to do scans of tables. 
Ideally, this would be in a measure, not a calculated column, but I don't know your requirements. Reporting the date in a visual can be done by a measure. If you need the date in a slicer or report filter, then it must go in a column.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

If I've answered your question, please mark as the solution. If not, can you provide some sample data with expected output?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

good day @edhans.,

You are really an expert, thanks so much!

I maneged to solve the issue, together with the info of the other advisor.

Thanks again!

John

[edhans]

Great @Anonymous - glad I was able to assist.