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

Sorry my explanation was not clear enough; I will remember that going forward.

Here is a link to an excel file with 2 queries in it where I am trying to join the fiscal periods table to the -Dimdates table via PowerQuery.  

 

Excel File Link

jennratten's avatar
jennratten
Super User
4 years ago

Hello - I recommend you create a new dataflow which contain linked entities for the DimDate and Fiscal Date tables.  Then create a computed entity from DimDate and add the following to filter the DimDate range based on the Fiscal min/max dates:

 

DimDate

 

Fiscal Dates

 

RESULT

 

 

SCRIPT

let
Source = DimDate,
Filter = Table.SelectRows (
        Source,                                                  // Name of the table from the prior step
        let                                                                    // declare variables with the scope table (ChangeTypes)
            minDate = List.Min ( FiscalDates[Date] ),  // TableName[ColumnName], returns a list of dates
            maxDate = List.Max ( FiscalDates[Date] )  // TableName[ColumnName], returns a list of dates
        in                                                                      // end the declaration at the table scope level
            each                                                             // iterate each row
            [Date] >= minDate                                      // compare the date in each row to the declared min/max
            and [Date] <= maxDate
    )
in 
Filter