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.

[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