Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Is it possible to create something like a measure for dataflows?
I have two dataflows
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?
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
- 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSorry 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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank You! This solved my problem. Great Work!
Glad I was able to help Scott.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello 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
I am trying to find the min open year ie 2022 like in the below pic.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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.
Here is the Query in full
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks @edhans - I overlooked the obvious Thanks Again - we have a complete Dataflow solution now for a comprehensive dates table.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
23 | |
18 | |
12 |