Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I have an issue and I will apreciate your Helps!
I need to calculate the DateEndPhase column using Power Query (M) based on the DateCreated column of the next row for the same Taskname as shown in this table :
Thanks in advance for your replies,
Taddi10
Solved! Go to Solution.
If your data source isn't too large, you can use this method. Add two INDEX columns, one that starts at 0, one that starts at 1.
Then merge the table to itself like this:
When you expand the Date Created column, that will be your End Phase Date. Here is the table immedately after the merge - note that the sort order changes. We'll fix that in a minute.
Now just sort by the task and date created fields, and keep only the necessary fields.
Here is the M code to see how it works:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTLUN9Q3MjAyADKVYnUgYkZoYklAhjEWMRM0sWQgwxRNLAXIMMMiZo4sFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskName = _t, #"Date Created" = _t, Phase = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"TaskName", "Index.1"}, #"Added Index1", {"TaskName", "Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Date Created"}, {"Date Created.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Date Created.1", "End Date"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"TaskName", Order.Ascending}, {"Date Created", Order.Ascending}}),
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"TaskName", "Date Created", "Phase", "End Date"})
in
#"Removed Other Columns"
If this is slow @Anonymous let me know. There is another way to do this. It is MUCH more complex - requires manual coding. Still, not overly ornerous, but not necessary for a few hundred or even a few thousand records. But it would perform well over hundreds of thousands of records.
Also, in the future, please post actual data to use - images are great for expected results, but we have to key stuff in.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you it works !
Thank you it works !
This misses the mark as the Phase data is removed. Also will be a challenge when there are other columns involved, especially in case example where we are looking at a COGS column changing, etc.
No it doesn't. I never populated the phase column. The OP gave me an image, not a table, so I didn't key in what wasn't necessary. It wasn't relevant to the question.
If you have a specific scenario, please post a new question as this one has been marked as solved and solved questions don't get as much traffic as unanswered ones.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportinglet
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjAyUIrVgQgYIQs4oasACxhgiJgiizgDRVAEXMDGGqEKmMIFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskName = _t, #"Date Created" = _t]),
#"Grouped Rows" = Table.RemoveColumns(Table.Group(Source, {"TaskName"}, {{"ar", each Table.FromColumns(Table.ToColumns(_) & {List.Skip([Date Created],1) & {null}}, Table.ColumnNames(_) & {"DateEndPhase"})}}), "TaskName"),
#"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"TaskName", "Date Created", "DateEndPhase"}, {"TaskName", "Date Created", "DateEndPhase"})
in
#"Expanded ar"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
If your data source isn't too large, you can use this method. Add two INDEX columns, one that starts at 0, one that starts at 1.
Then merge the table to itself like this:
When you expand the Date Created column, that will be your End Phase Date. Here is the table immedately after the merge - note that the sort order changes. We'll fix that in a minute.
Now just sort by the task and date created fields, and keep only the necessary fields.
Here is the M code to see how it works:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTLUN9Q3MjAyADKVYnUgYkZoYklAhjEWMRM0sWQgwxRNLAXIMMMiZo4sFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskName = _t, #"Date Created" = _t, Phase = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"TaskName", "Index.1"}, #"Added Index1", {"TaskName", "Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Date Created"}, {"Date Created.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Date Created.1", "End Date"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"TaskName", Order.Ascending}, {"Date Created", Order.Ascending}}),
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"TaskName", "Date Created", "Phase", "End Date"})
in
#"Removed Other Columns"
If this is slow @Anonymous let me know. There is another way to do this. It is MUCH more complex - requires manual coding. Still, not overly ornerous, but not necessary for a few hundred or even a few thousand records. But it would perform well over hundreds of thousands of records.
Also, in the future, please post actual data to use - images are great for expected results, but we have to key stuff in.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans, can you please give me the idea for large dataset? I am working on a dataset which have 100K records.
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.