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 made both of the chanages mentioned above. This is what I have currently, with the following errors.  Please see the photos below. 

 

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

Hi KarlConstruct ,

 

Sorry for our mistake, please try to use the follwing sub query, also very appreciate danextian  can point the error.

 

 

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
)

 

 

We do not need to change the type of Submittal ID column, the Value is the column name of record.

 

All the queries are here:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc65DcAgEADBXi4G6R7eWhD9t2HLThCbTjRriUkSV5tZLavLThcFqZAqqX3kJ3XSIE2QKclI/z5OClIhVVIjddK73w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Submittal ID" = _t, #"Sent Date" = _t]),
    #"Changed Type2" = Table.TransformColumnTypes(Source,{{"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


Best regards,

 

  • 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,