Remove the date filter and show all columns

[GFire]

I've developed the following code and need it to remove the date filter at the end and display all the columns in the dataset.

The code's function is to remove duplicate records [N_Invoice] while keeping the one with the highest index number [Index].

let
Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"N_Invoice", type text}, {"Index", Int64.Type}, {"Fecha", type date}}),
#"FilteredRows" = Table.SelectRows(#"Changed Type", each Date.IsInCurrentMonth([Fecha]) or Date.IsInPreviousNMonths([Fecha], 11)),
//Group by N_invoice and retain the last row
#"Grouped Rows" = Table.Group(#"FilteredRows", {"N_Invoice"}, {{"Latest", each Table.Last(_), type [N_Invoice=nullable text, Index=nullable number]}}),
//remove column and re-expand
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"N_Invoice"}),
#"Expanded Latest" = Table.ExpandRecordColumn(#"Removed Columns", "Latest", {"N_Invoice", "Index"}, {"N_Invoice", "Index"})
in
#"Expanded Latest"

Link to Dataset

https://drive.google.com/drive/folders/1LanSPa_O1_qirmJYPIBh1IaJQkMJ910_?usp=sharing

Thank you.

[v-karpurapud]

Hi @GFire 
Welcome to the Microsoft Fabric Community Forum.

To remove the date filter and display all columns, please use the following updated Power Query M code.

let
    
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "bdRNasQwDIbhu2Q9hUi2/i7QI3QzzP2v0Uj5aC3iRaH4xbH8kMn7fXz/fJ0+p8XxOs7rb4qZH5/XX3G+VinL1Ov/tcxrlXcl5Fodu6eF5uq22LUqmyJnzqYosRbKie0ufq6Fcy5Hocd9AuV5HwKCj8eFiJDmepLk+cRI0lIOQHBwXZNWAoTbmiw3ECS8SVhNCApvFFYTwiKaheUGAkZQSzU8NGLVEM9dDI1YNfTMVYZGrBrKOTxDI6Slel2gEdpSDs/QCGsph2NoxKqhI4dnaMSqobMmvDX8PFvKudmRqKWaMJC4pXqjT6SmMXOCQUhNY+YEg5GaxsxjxkBqGpLHjInUNCRXhyA1DakxFKlpaE4woEFNQ+uB0KCmoXVlaFDTsLotNKhp1Bs1oUFNw+oYaFDT8HoWNKhp+P2FQWoafm9AahpRu6BBTSNqFzS4aUQdAw1uGvV5mtDgVcPq5yDQ4KeGQIOfGoJfyvSHhoxtyg0CDV6h7g+byDblBtFdqi+b2DblBvFtqnvFf/r8Ag==",
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) 
        in type table [N_Invoice = _t, Index = _t, Fecha = _t]
    ),

    
    #"Changed Type" = Table.TransformColumnTypes(Source, {
        {"N_Invoice", type text}, 
        {"Index", Int64.Type}, 
        {"Fecha", Int64.Type}
    }),

   
    #"Converted Fecha" = Table.TransformColumns(#"Changed Type", {
        {"Fecha", each Date.From(_), type date}
    }),

    
    #"Grouped Rows" = Table.Group(#"Converted Fecha", {"N_Invoice"}, {
        {"MaxIndexRow", each Table.Max(_, "Index"), type [Index=Int64.Type, Fecha=date]}
    }),

    #"Expanded MaxIndexRow" = Table.ExpandRecordColumn(#"Grouped Rows", "MaxIndexRow", {"Index", "Fecha"})
in
    #"Expanded MaxIndexRow"

I have updated the .PBIX file to demonstarte this logic. Please take a moment to review the transformations in the Power Query Editor to ensure everything aligns with your reporting needs.

If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.

Thank you for being part of Fabric Community Forum.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[GFire]

Hello v-karpurapud,

The solution you proposed is one step away from being correct; it just needs to remove the date filter. The table contains records from 2022, and they don't appear in the output table. Note that in my code, I remove duplicates within a range (the last 12 months), and once they're removed, the table expands and should remove the date filter and display all the remaining records.

[v-karpurapud]

Hi @GFire 

Please try the below M code:

let
    
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "bdRNasQwDIbhu2Q9hUi2/i7QI3QzzP2v0Uj5aC3iRaH4xbH8kMn7fXz/fJ0+p8XxOs7rb4qZH5/XX3G+VinL1Ov/tcxrlXcl5Fodu6eF5uq22LUqmyJnzqYosRbKie0ufq6Fcy5Hocd9AuV5HwKCj8eFiJDmepLk+cRI0lIOQHBwXZNWAoTbmiw3ECS8SVhNCApvFFYTwiKaheUGAkZQSzU8NGLVEM9dDI1YNfTMVYZGrBrKOTxDI6Slel2gEdpSDs/QCGsph2NoxKqhI4dnaMSqobMmvDX8PFvKudmRqKWaMJC4pXqjT6SmMXOCQUhNY+YEg5GaxsxjxkBqGpLHjInUNCRXhyA1DakxFKlpaE4woEFNQ+uB0KCmoXVlaFDTsLotNKhp1Bs1oUFNw+oYaFDT8HoWNKhp+P2FQWoafm9AahpRu6BBTSNqFzS4aUQdAw1uGvV5mtDgVcPq5yDQ4KeGQIOfGoJfyvSHhoxtyg0CDV6h7g+byDblBtFdqi+b2DblBvFtqnvFf/r8Ag==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let _t = ((type nullable text) meta [Serialized.Text = true])
        in type table [N_Invoice = _t, Index = _t, Fecha = _t]
    ),

   
    #"Changed Type" = Table.TransformColumnTypes(Source, {
        {"N_Invoice", type text},
        {"Index", Int64.Type},
        {"Fecha", Int64.Type}
    }),

   
    #"Converted Fecha" = Table.TransformColumns(#"Changed Type", {
        {"Fecha", each Date.From(_), type date}
    }),

   
    #"Filtered Last 12 Months" = Table.SelectRows(#"Converted Fecha", each [Fecha] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -12)),

    #"Grouped Filtered Rows" = Table.Group(#"Filtered Last 12 Months", {"N_Invoice"}, {
        {"Keep", each Table.Max(_, "Index"), type [N_Invoice = text, Index = Int64.Type, Fecha = date]}
    }),

   
    #"Kept Invoice List" = Table.SelectColumns(#"Grouped Filtered Rows", {"N_Invoice"}),

 
    #"Join with Full Data" = Table.NestedJoin(#"Converted Fecha", {"N_Invoice"}, #"Grouped Filtered Rows", {"N_Invoice"}, "Matched", JoinKind.LeftOuter),
    #"Expanded Matched" = Table.ExpandTableColumn(#"Join with Full Data", "Matched", {"Index", "Fecha"}, {"MaxIndex", "MaxFecha"}),


    #"Final Filtered Table" = Table.SelectRows(#"Expanded Matched", each
        [MaxIndex] = null or [Index] = [MaxIndex]
    ),

    
    #"Removed Temp Columns" = Table.RemoveColumns(#"Final Filtered Table", {"MaxIndex", "MaxFecha"})
in
    #"Removed Temp Columns"

If this helps, kindly mark it as Accepted Solution.


Thank You.

[mromain]

Hi GFire

Here a possible solution:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"N_Invoice", type text}, {"Index", Int64.Type}, {"Fecha", type date}}),
    GroupByInvoice = Table.Group(ChangedType, {"N_Invoice"}, {{"MaxIndex", each List.Max([Index]), type nullable number}}),
    MergeData = Table.NestedJoin(GroupByInvoice, {"N_Invoice", "MaxIndex"}, ChangedType, {"N_Invoice", "Index"}, "Data", JoinKind.LeftOuter),
    SelectColumnData = Table.SelectColumns(MergeData,{"Data"}),
    ExpandColumnsData = Table.ExpandTableColumn(SelectColumnData, "Data", {"N_Invoice", "Index", "Fecha"}, {"N_Invoice", "Index", "Fecha"})
in
    ExpandColumnsData

[GFire]

Hi mromain,

The table contains records from 2022, and they don't appear in the output table. Note that in my code, I remove duplicates within a range (the last 12 months). Once they're removed, the table expands and should remove the date filter and display all the remaining records.