Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cannot convert Type to Type List

Hello,

 

I want to remove all the records from a table called F_SOURCE_DAILY except the most recent date and the 4 following dates (in total 5 consequal dates)

The only field that indicates the date is the field FK_DATE. The field FK_DATE is Whole Number and it's type should not be changed

Example:

 

In this example is the most recent date 20200820 (August 20, 2020).
In this case I want to keep the records from 20200816 till 20200820   (August 16, 2020 till August 20, 2020)

Result:

Remark that 20200815 is not included because it is not in {20200820, 20200819, 20200818, 20200817, 20200816,}

I want to apply a Filtered Rows step to do this.

To find the most recent date in the tabel, I could use in the Power Query the functions List.Max() or List.MaxN()

I tried this expression:

= Table.SelectRows(F_SOURCE_DAILY,

each (#date(

Int32.From(Text.Middle(Text.From([FK_DATE]),0,4)),
Int32.From(Text.Middle(Text.From([FK_DATE]),4,2)),
Int32.From(Text.Middle(Text.From([FK_DATE]),6,2)))

>= Date.AddDays(

#date(Int32.From(Text.Middle(Text.From(List.Max([FK_DATE])),0,4)),
          Int32.From(Text.Middle(Text.From(List.Max([FK_DATE])),4,2)),
          Int32.From(Text.Middle(Text.From(List.Max([FK_DATE])),6,2))), -4)))

 

I get an error:

 

I also tried this expression:


= Table.SelectRows(F_SOURCE_DAILY, each ([FK_DATE] = List.MaxN([FK_DATE],5)))


but that gives the error:

 

 

Anybody an idea how to solve this?

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hello @RudyWelvaert

    you'd have to adjust the syntax to this:

    Table. SelectRows (F_SOURCE_DAILY, each each ([FK_DATE] á List. MaxN (F_SOURCE_DAILY[FK_DATE], 5)))

    For performance reasons, it would probably make sense to buffer the F_SOURCE_DAILY table before feeding in this step.

    Within the 20th argument of Table.Select Rows, the short form [FK_Date] returns the record field (the current row) and not the entire column. Therefore, you must explicitly type the name of the table whose column you want to choose for List.Max.

    @FarhanAhmed ,

    just a warning: Sort commands in Power Query do not reliably maintain sort order without a buffer: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390


4 Replies

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    6 years ago

    Try this

     

    You need to sort your column

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\farhan.ahmed\Desktop\Book1.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    
    #"Sorted Rows" = Table.Sort(Table1_Table,{{"FK_Date", Order.Descending}}),

    Distincttable =  Table.Distinct(Table.SelectColumns(#"Sorted Rows","FK_Date")),
    TopTable = Table.FirstN(Distincttable,5),
   
   
    FilteredTable = Table.NestedJoin(#"Sorted Rows", {"FK_Date"}, TopTable, {"FK_Date"}, "Table1", JoinKind.Inner),
    RemoveColumn = Table.RemoveColumns(FilteredTable,{"Table1"})
in
    RemoveColumn

     

    • ImkeF's avatar
      ImkeF
      Community Champion
      6 years ago

      Hello @RudyWelvaert

      you'd have to adjust the syntax to this:

      Table. SelectRows (F_SOURCE_DAILY, each each ([FK_DATE] á List. MaxN (F_SOURCE_DAILY[FK_DATE], 5)))

      For performance reasons, it would probably make sense to buffer the F_SOURCE_DAILY table before feeding in this step.

      Within the 20th argument of Table.Select Rows, the short form [FK_Date] returns the record field (the current row) and not the entire column. Therefore, you must explicitly type the name of the table whose column you want to choose for List.Max.

      @FarhanAhmed ,

      just a warning: Sort commands in Power Query do not reliably maintain sort order without a buffer: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390


      • edhans's avatar
        edhans
        Community Champion
        6 years ago

        Anonymous I took a slightly different approach using List.Contains. 

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc67CcAwDAXAXVS7kJ5/8izG+68RUCACJQS1V93eJFQIDGYF0ymbEKE+IMugRegOajAiTIdpoBGWwzAQfolfpd+CP2kJqQlBQiQh/CXnAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DateKey = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateKey", Int64.Type}}),
    #"Keep 5 Days" = 
        Table.SelectRows(
            #"Changed Type",
            each
                let
                    varLastDate = List.Max(#"Changed Type"[DateKey]),
                    varKeepDays = {varLastDate - 4..varLastDate}
                in
            List.Contains(varKeepDays, [DateKey])
        )
in
    #"Keep 5 Days"
        • varLastDate equals 20200820
        • varKeepDays returns a list of integers from 20200816 through 20200820
        • ListContains is your Select Rows filter condition - it keeps everything in your table from the #Changed Step step that exists in the varKeepDays list.

        I think it depends on what you need. The List.MaxN will keep the most recent 5 days in the list, which would be 20, 19, 18, 17, and 15. I don't see a 16 in your data. My method will keep 5 calendar days, so 20, 19, 18, 17, and 16, even though there is no 16 in the data.