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.
Hi to all,
First of all, thank you for the great forum. Usually, I can find the questions here without needing to post.
I have the following data:
Unique tag Date Entry data date
UT1 1/1/2020 1/12/2019
UT1 2/1/2020 4/12/2019
UT1 5/1/2020 6/12/2019
UT1 3/1/2020 8/12/2019
UT2 2/2/2020 1/12/2019
UT2 15/2/2020 4/12/2019
UT2 10/2/2020 6/12/2019
I would like to find a way to add a new column that would have the difference between Date on that row and Date on the previous row. For example, first-line would be blank, because that is the first line with that Tag. 2nd line would be 2/2/2020 - 1/1/2020, i.e., 1,0.
The Rows are ordered chronologically, based on Entry date value.
Could someone kindly help me.
Thank you,
João
Solved! Go to Solution.
Got it!
So here is what I did:
Hi @JoaoPvP ,
You could create a custom function to get previous row. Here is the link for your reference:
let func =
(Table as table, optional Step as number, optional SelectedColumns, optional GroupByColumns, optional Suffix as text, optional Buffer as any) =>
let
// Steps to prepare the (optional) parameters for the nested function "fnFetchNextRow"
Source = if Buffer = null then Table else Table.Buffer(Table),
Step0 = if Step = null then -1 else Step,
Step_ = if Step = null then 1 else Number.Abs(Step),
Suffix = if Suffix = null then ".Prev" else Suffix,
GroupByColumns = if GroupByColumns = null then null else GroupByColumns,
ShiftFunction = if Step0 < 0 then Table.RemoveLastN else Table.RemoveFirstN,
ColNames = List.Buffer(Table.ColumnNames(Source)),
NewColNames = if SelectedColumns = null then ColNames else SelectedColumns,
CountNewCols = List.Count(NewColNames),
// Core function that retrieves values from previous or next rows (depending on sign of parameter "Step")
fnFetchNextRow = (Table_ as table, optional Step as number, optional SelectedColumns, optional Suffix as text, optional Buffer as any) =>
let
MergeTable = if SelectedColumns = null then Table_ else Table.SelectColumns(Table_, SelectedColumns),
Shift = if Step0 > 0 then ShiftFunction(MergeTable, Step_) & #table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_))
else #table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_)) & ShiftFunction(MergeTable, Step_),
Reassemble = Table.ToColumns(Table_) & Table.ToColumns(Shift),
Custom1 = Table.FromColumns( Reassemble, Table.ColumnNames(Source) & List.Transform(NewColNames, each _&Suffix ) )
in
Custom1,
// optional grouping on certain columns
#"Grouped Rows" = Table.Group(Source, GroupByColumns, {{"All", each _}}, GroupKind.Local),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fnFetchNextRow([All], Step0, SelectedColumns, Suffix, Buffer)),
#"Removed Columns" = Table.Combine(Table.RemoveColumns(#"Added Custom", GroupByColumns & {"All"})[Custom]),
// case no grouping
NoGroup = fnFetchNextRow(Source, Step0, SelectedColumns, Suffix, Buffer),
// select case grouping
Result = if GroupByColumns = null then NoGroup else #"Removed Columns"
in
Result ,
documentation = [
Documentation.Name = " Table.ReferenceDifferentRow ",
Documentation.Description = " Adds columns to a <code>Table</code> with values from previous or next rows (according to the <code>Step</code>-index in the 2nd parameter) ",
Documentation.LongDescription = " Adds columns to a <code>Table</code> with values from previous or next rows (according to the <code>Step</code>-index in the 2nd parameter) ",
Documentation.Category = " Table ",
Documentation.Source = " ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann (www.TheBIccountant.com ) ",
Documentation.Examples = {[Description = " ",
Code = " Table.ReferenceDifferentRow( #table( {""Product"", ""Value""}, List.Zip( { {""A"" ,""A"" ,""B"" ,""B"" ,""B""}, {""1"" ,""2"" ,""3"" ,""4"" ,""5""} } ) ) ) ",
Result = " #table( {""Product"", ""Value"", ""Product.Prev"", ""Value.Prev""}, List.Zip( { {""A"" ,""A"" ,""B"" ,""B"" ,""B""}, {""1"" ,""2"" ,""3"" ,""4"" ,""5""}, {null ,""A"" ,""A"" ,""B"" ,""B""}, {null ,""1"" ,""2"" ,""3"" ,""4""} } ) ) "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
And you could get the result in DAX easily with EARLIER() function.
Please see this M code for an example. The example data didn't copy well so there are extra steps in the beginning to clean it up.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg0xVMANDPUN9Y0MjAzwqjACKjG0VIrViVYCE/iNNCJopAmpRpoSMtKMVBONCZlogc1EIzwmAlXjNRFrOOIz0NAUv4lYQxGvgQYEnIgUirEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique tag Date Entry data date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique tag Date Entry data date", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Unique tag Date Entry data date] <> "")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows"," "," ",Replacer.ReplaceText,{"Unique tag Date Entry data date"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," "," ",Replacer.ReplaceText,{"Unique tag Date Entry data date"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," "," ",Replacer.ReplaceText,{"Unique tag Date Entry data date"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2"," "," ",Replacer.ReplaceText,{"Unique tag Date Entry data date"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3"," "," ",Replacer.ReplaceText,{"Unique tag Date Entry data date"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value4", "Unique tag Date Entry data date", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Unique tag Date Entry data date.1", "Unique tag Date Entry data date.2", "Unique tag Date Entry data date.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Unique tag Date Entry data date.1", type text}, {"Unique tag Date Entry data date.2", type text}, {"Unique tag Date Entry data date.3", type date}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"Unique tag Date Entry data date.2", type date}}, "en-150"),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"Unique tag Date Entry data date.1", "Unique Tag"}, {"Unique tag Date Entry data date.2", "Date"}, {"Unique tag Date Entry data date.3", "Entry data date"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Difference", each try Duration.Days([Date] - #"Added Index"[Date]{[Index] -1}) otherwise null)
in
#"Added Custom"
To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with this M code.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat , thank you for getting back to me.
I believe I have made at least 2 mistakes when explaining this.
1-The date is coming from an excel file and looks like this:
Unique Tag | Date | Entry data date |
UT1 | 1/1/2020 | 1/12/2019 |
UT1 | 2/1/2020 | 4/12/2019 |
UT1 | 5/1/2020 | 6/12/2019 |
UT1 | 3/1/2020 | 8/12/2019 |
UT2 | 2/2/2020 | 1/12/2019 |
UT2 | 15/2/2020 | 4/12/2019 |
UT2 | 10/2/2020 | 6/12/2019 |
2- I've oversimplified the problem and I'm not sure your code is taking this into account. Sometimes we have only 1 entry and other times we have more than 20.
I kindly ask you to check if the initial steps are still required and could you please teach me what your coding is doing?
Thank you very much,
João
Dunno why, but the table I've inserted lost the formating. It is a simple table with 3 columns.
I looked at your initial post and see I misread it. The 2/2 date comes from the next Unique Tag. Please see this M code instead. Still not 100% clear on your request. This compares the earliest Date row for each Unique Tag. I also got rid of the extra code for data clean up, so this starts with your example data.
If this is not what you are looking for, please share a table with the full expected output from this example dataset.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg0xVNJRMtQ31DcyMDKAMI2AbENLpVgdhLQRTNoEq7QpTNoMq7QxTNoCTdoIKGaEMBzdboi0oSkuy6HyBlhsjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Tag" = _t, Date = _t, #"Entry data date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique Tag", type text}, {"Date", type date}, {"Entry data date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Unique Tag"}, {{"AllRows", each _, type table [Unique Tag=text, Date=date, Entry data date=date]}}),
FirstRow = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.First(_)}}),
#"Expanded AllRows" = Table.ExpandRecordColumn(FirstRow, "AllRows", {"Date", "Entry data date"}, {"Date", "Entry data date"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded AllRows", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Days From Previous Date", each try Duration.Days([Date]-#"Expanded AllRows"[Date]{[Index]-1}) otherwise null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Days from Previous Data Entry Date", each try Duration.Days([Entry data date]-#"Expanded AllRows"[Entry data date]{[Index]-1}) otherwise null),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Unique Tag", "Date", "Entry data date", "Days From Previous Date", "Days from Previous Data Entry Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Unique Tag", type text}, {"Date", type date}, {"Entry data date", type date}, {"Days From Previous Date", Int64.Type}, {"Days from Previous Data Entry Date", Int64.Type}})
in
#"Changed Type1"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat ,
Thank you for your time.
I cannot put your code to run. I'm creating a blank query and putting your code, but it just converts to text.
Can we find a way to do this in DAX?
I was thinking, maybe we could create an index for each unique tag (like a count how many times this has appeared until now) Since the data is in chronological order, then I would just filter the information on index x to get the data from index x-1.
What do you think?
@v-eachen-msft , how would the earlier work?
Thank you.
Got it!
So here is what I did:
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.