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

Hello, 

 

I incorporated your formulas into my advanced editor and then i changed some fields to match my column names, but i am recieving some errors. Please see screenshots below. 

 

Any help would be much appreciated. 

 

Advanced editor (source removed)

 

Submittal ID column was not found. 

However its not being found

 

However it is a column in the data. 

Submittal ID is a column

 

Overall test recieves and error as well. 

 

Any idea what i'm doing wrong?  Please let me know if I need to provide anything else to troubleshoot this issue im having. 

 

Thanks again. 

KarlConstruct's avatar
KarlConstruct
Frequent Visitor
6 years ago

update to the advanced editor, still recieving the errors. 

 

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

    Hi KarlConstruct ,

     

    we can try to change the "Date" to "Sent Date" as following picture to fix this problem.

     


    Best regards,

     

     

  • danextian's avatar
    danextian
    Super User
    6 years ago

    Could you check on this?

    n = [Submittal ID],
t = Table.SelectRows(#"Changed Type", each [Submittal ID] = n),

     

    n references [Submittal ID] and [Submittal ID] references n.

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