Forum Discussion

ScottBrown's avatar
ScottBrown
Helper II
4 years ago
Solved

Measure Capability in Dataflows

Is it possible to create something like a measure for dataflows? I have two dataflows Dates Table Fiscal Year Calendar I can mash this up in Dax but want it in PowerQuery to create a self-servi...
Need Help
  • edhans's avatar
    edhans
    4 years ago

    See this ScottBrown 
    it does a series of calculations in the Dim Date Table with some self-merges of different steps. It returns these  columns. Your date table has more than the fiscal table, so not everything is there - no 2018 for example.

    You can see in the steps I broke it into two areas as there needed to be a different way to get fiscal period vs Year status.

    The file is here https://1drv.ms/x/s!AheFG2CwN3xnivI023Lxboobm6MVwQ?e=VOzs2N

     

    If that isn't what you want, and you cannot modify my code to suit your needs, please provid a mock up in excel of the desired results.

     

  • edhans's avatar
    edhans
    3 years ago

    I think this is what you want ScottBrown 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlHSUVKK1QGzjYFs/4LUPBjfCI1vCOR7ZBaXYPJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    AddedMinYear =
        Table.AddColumn(
            #"Changed Type",
            "Min Year",
            each
                List.Min(
                    Table.SelectRows(#"Changed Type", each [Status] = "Open")[Year]
                )
        )
in
    AddedMinYear

    Your general idea was right, but the syntax was off.
    For this table this works fine, but this logic will not work at all in a table with a few thousand records. Power Query is horrible at table scans like this. DAX is what works best, but for 3 records or even 300, Power Query is fine.

ScottBrown's avatar
ScottBrown
Helper II
4 years ago

Thank You!  This solved my problem.  Great Work!

edhans's avatar
edhans
Community Champion
4 years ago

Glad I was able to help Scott.

  • ScottBrown's avatar
    ScottBrown
    Helper II
    3 years ago

    Hello edhans,,

    In your above solution that you helped me with prior.

    I need to create a Fiscal Year Status column.  The problem is the Year Status column showing as open needs to be the min year.  Folks may key in dates for the next fiscal year and it shows as open when working in the current fiscal year.

     

    The logic would be something like this:

    2024 = Future Year

    2023 = Next Year (Shows in system as Open Year)

    2022 = Open Year

    2020 = Last Year

    2019 = Prior Years

     

     

     

     

    • ScottBrown's avatar
      ScottBrown
      Helper II
      3 years ago

      I am trying to find the min open year ie 2022 like in the below pic.

      • edhans's avatar
        edhans
        Community Champion
        3 years ago

        I think this is what you want ScottBrown 

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlHSUVKK1QGzjYFs/4LUPBjfCI1vCOR7ZBaXYPJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    AddedMinYear =
        Table.AddColumn(
            #"Changed Type",
            "Min Year",
            each
                List.Min(
                    Table.SelectRows(#"Changed Type", each [Status] = "Open")[Year]
                )
        )
in
    AddedMinYear

        Your general idea was right, but the syntax was off.
        For this table this works fine, but this logic will not work at all in a table with a few thousand records. Power Query is horrible at table scans like this. DAX is what works best, but for 3 records or even 300, Power Query is fine.