Forum Discussion

KarlConstruct's avatar
KarlConstruct
Frequent Visitor
6 years ago

Need Help Writing a Dated Difference Formula

I have a txt file brought into PowerBI via a sharepoint folder, and its a list of transmitted documents and the dates in which each transmission happened.  I don't necessarily care about all of the c...
Need Help
KarlConstruct's avatar
KarlConstruct
Frequent Visitor
6 years ago

I still seem to be getting the same errors as before unfortunately. 

 

I have created a pbix file with all of my code in the advance editor, if you could please take a look. I would really appreciate it. 

 

Dated Diff pbix 

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi KarlConstruct ,

 

Sorry for my mistake, we found that error is in #"Changed Type2" step, it should call the previous step name, in our sample is Source, but in yours in #"Changed Type1"

 

    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Submittal ID", type number}, {"Sent Date", type date}}),

 

ALL the queries are here ( we delete some information)

 

let
    Source = Excel.Workbook(File.Contents("File_Path"), null, true),
    #"Submittal Query (2)_Sheet" = Source{[Item="Submittal Query (2)",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Submittal Query (2)_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Submittal Package .", type any}, {"Author Contact ID", type text}, {"Description", type text}, {"Submittal ID", Int64.Type}, {"Prolog Sequence .", Int64.Type}, {"Prolog Spec .", type any}, {"Prolog Revision .", Int64.Type}, {"Is Closed", Int64.Type}, {"Sent Date", type datetime}, {"From Contact ID", type text}, {"To Contact ID", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Sent Date", type text}}, "en-US"), "Sent Date", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Sent Date.1", "Sent Date.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Sent Date.1", "Sent Date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Submittal ID", type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Submittal ID", type number}, {"Sent Date", type date}}),
    Test = Table.SelectRows(
    #"Changed Type2",
    each let d = [Sent Date],
             n = [Submittal ID],
             t = Table.SelectRows(#"Changed Type2", each [Submittal ID]=n),
                 isMin = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Min(Table.SelectColumns(t,"Sent Date"),"Sent Date")),"Value"),{{"Value",type text}})),Date.ToText(d)),
                 isMax = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Max(Table.SelectColumns(t,"Sent Date"),"Sent Date")),"Value"),{{"Value",type text}})),Date.ToText(d))
         in 
             isMin or isMax
)
in
    Test

 

We noticed that the Sent Date used to be a datetime value and then  you split it into date and time columns in your queries, if you want to keep the origin Sent DateColumn as datetime, we can change the query,

 

let
    Source = Excel.Workbook(File.Contents("File_Path"), null, true),
    #"Submittal Query (2)_Sheet" = Source{[Item="Submittal Query (2)",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Submittal Query (2)_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Submittal Package .", type any}, {"Author Contact ID", type text}, {"Description", type text}, {"Submittal ID", Int64.Type}, {"Prolog Sequence .", Int64.Type}, {"Prolog Spec .", type any}, {"Prolog Revision .", Int64.Type}, {"Is Closed", Int64.Type}, {"Sent Date", type datetime}, {"From Contact ID", type text}, {"To Contact ID", type text}})
    Test = Table.SelectRows(
    #"Changed Type",
    each let d = [Sent Date],
             n = [Submittal ID],
             t = Table.SelectRows(#"Changed Type2", each [Submittal ID]=n),
                 isMin = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Min(Table.SelectColumns(t,"Sent Date"),"Sent Date")),"Value"),{{"Value",type text}})),DateTime.ToText(d)),
                 isMax = List.Contains(Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Record.ToTable(Table.Max(Table.SelectColumns(t,"Sent Date"),"Sent Date")),"Value"),{{"Value",type text}})),DateTime.ToText(d))
         in 
             isMin or isMax
)
in
    Test


Best regards,