Measure Capability in Dataflows

[ScottBrown]

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-service BI tool.

How can I get a value from the Fiscal year calendar dataflow and filter it in the Dates Table?

• Fiscal Year  = 10/01/21 to 09/30/2022 = FY22
  • Need to add this to the Dates Table
• Need to this for the Fiscal Period and Min of Fiscal year and Max of Fiscal year to set the Dates - date range as well.

[edhans · Accepted solution]

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]

---

• Fiscal Year  = 10/01/21 to 09/30/2022 = FY22
  • Need to add this to the Dates Table
• Need to this for the Fiscal Period and Min of Fiscal year and Max of Fiscal year to set the Dates - date range as well.

---

What is "this" you want added? I started to mock this up and found I could do it 3-4 different ways, and still not guess what exactly you are talking about.

Please provide some sample data in Excel files for us to work with per specs below.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[ScottBrown]

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

[edhans · Accepted solution]

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.

[ScottBrown]

Thank You!  This solved my problem.  Great Work!

[edhans]

Glad I was able to help Scott.

[ScottBrown]

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]

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

[edhans · Accepted solution]

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]

I forgot to mention, is this the same syntax for a dataflow?

I am trying to keep a self contained solution.  It works in Desktop, having issues in cloud dataflows - running in premium capacity.

[edhans]

Same. Syntax in Dataflows and Power Query are identical. The only differences may be connectors themselves. On prem connectors need a gateway for dataflows, currently dataflows don't work with custom connectors, but by and large, it is 100% the same M language.

[ScottBrown]

I am missing something here.  I am getting null values.  I tried the first reference (Worked in Desktop) and than tried a second one to the original table and both produce nulls.

• List.Min(Table.SelectRows(#"Inserted Year", each [Year Status] = "Open")[Year])
• List.Min(Table.SelectRows(dim_SQL_XXXX_FiscalYearPeriods, each [Year Status] = "Open")[Fiscal Year])

Here is the Query in full

let

  // configurations start

  Today = Date.From(DateTime.LocalNow()),

  // today's date

  FromYear = 1997,

  // set the start year of the date dimension. dates start from 1st of January of this year

  ToYear = Date.Year(DateTime.FixedLocalNow())+1,

  // set the end year of the date dimension. dates end at 31st of December of this year

  StartofFiscalYear = 10,

  // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7

  firstDayofWeek = Day.Sunday,

  // set the week's start day, values: Day.Monday, Day.Sunday....

  // configuration end

  FromDate = #date(FromYear,1,1),

  ToDate = #date(ToYear,12,31),

  Source = List.Dates(

        FromDate,

        Duration.Days(ToDate-FromDate)+1,

        #duration(1,0,0,0)

    ),

  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

  #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),

  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),

  #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),

  #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),

  #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),

  #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),

  #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),

  #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),

  #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),

  #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),

  #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),

  #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date], firstDayofWeek), Int64.Type),

  #"Inserted Day of Week Order" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted Day of Week", "Day of Week Order", each Number.ToText([Day of Week],"00") & "-" &[Day Name]), {{"Day of Week Order", type text}}),

  #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week Order", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),

  #"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),

  #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),

  #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),

  #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),

  #"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date], firstDayofWeek), Int64.Type),

  #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date], firstDayofWeek), Int64.Type),

  #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date], firstDayofWeek), type date),

  #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date], firstDayofWeek), type date),

  #"Inserted Age" = Table.AddColumn(#"Inserted End of Week", "Age", each [Date] - Today, type duration),

  #"Extracted Days" = Table.TransformColumns(#"Inserted Age",{{"Age", Duration.Days, Int64.Type}}),

  #"Renamed Columns4" = Table.RenameColumns(#"Extracted Days", {{"Age", "Day Offset"}}),

  #"Inserted Month Offset" = Table.TransformColumnTypes(Table.AddColumn(#"Renamed Columns4", "Month Offset", each (([Year]-Date.Year(Today))*12)

+([Month]-Date.Month(Today))), {{"Month Offset", Int64.Type}}),

  #"Inserted Year Offset" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted Month Offset", "Year Offset", each [Year]-Date.Year(Today)), {{"Year Offset", Int64.Type}}),

  #"Inserted Quarter Offset" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted Year Offset", "Quarter Offset", each (([Year]-Date.Year(Today))*4)

+([Quarter]-Date.QuarterOfYear(Today))), {{"Quarter Offset", Int64.Type}}),

  #"Inserted Year-Month" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted Quarter Offset", "Year-Month", each Date.ToText([Date], "MMM yyyy")), {{"Year-Month", type text}}),

  #"Inserted Year-Month Code" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted Year-Month", "Year-Month Code", each Date.ToText([Date], "yyyyMM")), {{"Year-Month Code", Int64.Type}}),

  // START FISCAL PERIOD MASH-UP

  #"--> Fiscal Year" = dim_SQL_SAAB_FiscalYearPeriods,

  #"Removed Other Columns" = Table.SelectColumns(#"--> Fiscal Year", {"Fiscal Year", "Year Start Date", "Year End Date", "Year Status"}),

  #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),

  #"Added Year Range" = Table.AddColumn(#"Removed Duplicates", "Year Range", each List.Transform(

                {Number.From([Year Start Date])..Number.From([Year End Date])},

                each Date.From(_)

                ), type date),

  #"Expanded Year Range" = Table.ExpandListColumn(#"Added Year Range", "Year Range"),

  #"Merged Queries" = Table.NestedJoin(#"Inserted Year-Month Code", {"Date"}, #"Expanded Year Range", {"Year Range"}, "Year Range", JoinKind.LeftOuter),

  #"<--Expanded Year Range1" = Table.ExpandTableColumn(#"Merged Queries", "Year Range", {"Fiscal Year", "Year Status"}, {"Fiscal Year", "Year Status"}),

  #"--> Fiscal Periods" = dim_SQL_SAAB_FiscalYearPeriods,

  #"Removed Other Columns1" = Table.SelectColumns(#"--> Fiscal Periods", {"Fiscal Period", "Period Start Date", "Period End Date"}),

  #"Added Period Ranges" = Table.AddColumn(#"Removed Other Columns1", "Period Ranges", each List.Transform(

                    {Number.From([Period Start Date])..Number.From([Period End Date])},

                    each Date.From(_)

                ), type date),

  #"Expanded Period Ranges" = Table.ExpandListColumn(#"Added Period Ranges", "Period Ranges"),

  #"Merged Queries1" = Table.NestedJoin(#"<--Expanded Year Range1", {"Date"}, #"Expanded Period Ranges", {"Period Ranges"}, "Expanded Period Ranges", JoinKind.LeftOuter),

  #"<--Expanded Expanded Period Ranges" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Period Ranges", {"Fiscal Period"}, {"Fiscal Period"}),

  // END FISCAL PERIOD MASH-UP

  #"Filtered Rows" = Table.SelectRows(#"<--Expanded Expanded Period Ranges", each [Fiscal Year] <> null),

  #"Fiscal Quarter" = Table.AddColumn(#"Filtered Rows", "Fiscal Quarter", each if [Fiscal Period] >= 10 then "Q4" else if [Fiscal Period] >= 7 then "Q3" else if [Fiscal Period] >= 4 then "Q2" else if [Fiscal Period] >= 1 then "Q1" else null),

  #"Transform columns" = Table.TransformColumnTypes(#"Fiscal Quarter", {{"Fiscal Quarter", type text}}),

  Timeline_FY_FP_WK = Table.TransformColumnTypes(Table.AddColumn(#"Transform columns", "Timeline_FY_FP_WK", each Number.ToText([Fiscal Year]) & "-" & Number.ToText([Fiscal Period],"00") & "-" & Number.ToText([Week of Year], "00")), {{"Timeline_FY_FP_WK", type text}}),

  Timeline_FY_FP = Table.TransformColumnTypes(Table.AddColumn(Timeline_FY_FP_WK, "Timeline_FY_FP", each Number.ToText([Fiscal Year]) & "-" & Number.ToText([Fiscal Period],"00")), {{"Timeline_FY_FP", type text}}),

  #"Reordered Columns" = Table.ReorderColumns(Timeline_FY_FP, {"Date", "Year", "Start of Year", "End of Year", "Month", "Start of Month", "End of Month", "Days in Month", "Day", "Day Name", "Day of Week", "Day of Year", "Month Name", "Quarter", "Start of Quarter", "End of Quarter", "Week of Year", "Week of Month", "Start of Week", "End of Week", "Day Offset", "Month Offset", "Year Offset", "Quarter Offset", "Year-Month", "Year-Month Code", "Fiscal Year", "Fiscal Quarter", "Fiscal Period", "Year Status", "Timeline_FY_FP_WK", "Timeline_FY_FP"}),

   #"Sorted Rows" = Table.Sort(#"Reordered Columns", {{"Date", Order.Descending}}),

  #"Fiscal Year Open Year" = Table.TransformColumnTypes(Table.AddColumn(#"Sorted Rows", "Fiscal Year Open Year", each List.Min(Table.SelectRows(#"Reordered Columns", each [Year Status] = "Open")[Year])), {{"Fiscal Year Open Year", Int64.Type}})

in

  #"Fiscal Year Open Year"

[edhans]

Did you confirm the SQL table has a [Year Status] column and it contains the value "Open" ?

Remember, Power Query is case sensitive, so Open and OPEN are not the same.

[ScottBrown]

Thanks @edhans  - I overlooked the obvious  Thanks Again - we have a complete Dataflow solution now for a comprehensive dates table.

[ScottBrown]

Thanks @edhans - Your help and knowledge is awesome!  Thanks for the help.

[jennratten]

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