Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
GFire
Helper I
Helper I

Remove the date filter and show all columns

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.

1 ACCEPTED SOLUTION

Hi @GFire 

I have updated the .Pbix as per your requirement. Please take a movement to review it and let us know if you need any adjustments.

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}
    }),


    Today = Date.From(DateTime.LocalNow()),
    Last12Months = Date.AddMonths(Today, -12),

  
    RecentRows = Table.SelectRows(#"Converted Fecha", each [Fecha] >= Last12Months),

    MaxIndexPerInvoice = Table.Group(RecentRows, {"N_Invoice"}, {
        {"MaxIndex", each List.Max([Index]), Int64.Type}
    }),

   
    JoinOnMaxIndex = Table.NestedJoin(#"Converted Fecha", {"N_Invoice", "Index"}, MaxIndexPerInvoice, {"N_Invoice", "MaxIndex"}, "Matched", JoinKind.LeftOuter),

    WithFlag = Table.AddColumn(JoinOnMaxIndex, "Keep", each if Table.RowCount([Matched]) > 0 then true else null),

    #"Removed Matched" = Table.RemoveColumns(WithFlag, {"Matched"}),

    
    FinalFiltered = Table.SelectRows(#"Removed Matched", each [Keep] = true or [Fecha] < Last12Months),

   
    #"Removed Keep Column" = Table.RemoveColumns(FinalFiltered, {"Keep"}),

   
    Sorted = Table.Sort(#"Removed Keep Column", {{"N_Invoice", Order.Ascending}})
in
    Sorted

 

vkarpurapud_0-1751609742236.pngvkarpurapud_1-1751609746480.png


Thank you for being part of Fabric Community Forum.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

View solution in original post

11 REPLIES 11
v-karpurapud
Community Support
Community Support

Hi @GFire 

We have not yet received a response from you regarding if you find the response helpful, kindly mark it as the accepted solution and provide kudos, as this will aid other members with similar queries.

 

Thank You.

v-karpurapud
Community Support
Community Support

Hi @GFire 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

v-karpurapud
Community Support
Community Support

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.


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.

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.

screenshoot.png

Hi @GFire 

Try with the below M code:

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}
    }),


    FilteredLast12Months = Table.SelectRows(#"Changed Type", each [Fecha] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -12)),

    
    DedupedRecent = Table.Group(FilteredLast12Months, {"N_Invoice"}, {
        {"KeepRow", each Table.Max(_, "Index"), type [N_Invoice=nullable text, Index=nullable number, Fecha=nullable date]}
    }),

    RecentDuplicates = Table.SelectColumns(DedupedRecent, {"N_Invoice"}),

 
    Joined = Table.NestedJoin(#"Changed Type", {"N_Invoice"}, DedupedRecent, {"N_Invoice"}, "Recent", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "Recent", {"Index"}, {"MaxIndex"}),
    Filtered = Table.SelectRows(Expanded, each [MaxIndex] = null or [Index] = [MaxIndex]),

    FinalResult = Table.RemoveColumns(Filtered, {"MaxIndex"})
in
    FinalResult


Regards,
Karpurapu D.

It continues to give the same error. It doesn't delete the duplicate record with the lowest index, keeping the one with the highest index. The INPUT table has 60 records, and the OUTPUT table also has 60 records. Therefore, it doesn't delete any records.

 

GFire_0-1751284690712.png

 

Hi @GFire 

I have updated the .Pbix as per your requirement. Please take a movement to review it and let us know if you need any adjustments.

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}
    }),


    Today = Date.From(DateTime.LocalNow()),
    Last12Months = Date.AddMonths(Today, -12),

  
    RecentRows = Table.SelectRows(#"Converted Fecha", each [Fecha] >= Last12Months),

    MaxIndexPerInvoice = Table.Group(RecentRows, {"N_Invoice"}, {
        {"MaxIndex", each List.Max([Index]), Int64.Type}
    }),

   
    JoinOnMaxIndex = Table.NestedJoin(#"Converted Fecha", {"N_Invoice", "Index"}, MaxIndexPerInvoice, {"N_Invoice", "MaxIndex"}, "Matched", JoinKind.LeftOuter),

    WithFlag = Table.AddColumn(JoinOnMaxIndex, "Keep", each if Table.RowCount([Matched]) > 0 then true else null),

    #"Removed Matched" = Table.RemoveColumns(WithFlag, {"Matched"}),

    
    FinalFiltered = Table.SelectRows(#"Removed Matched", each [Keep] = true or [Fecha] < Last12Months),

   
    #"Removed Keep Column" = Table.RemoveColumns(FinalFiltered, {"Keep"}),

   
    Sorted = Table.Sort(#"Removed Keep Column", {{"N_Invoice", Order.Ascending}})
in
    Sorted

 

vkarpurapud_0-1751609742236.pngvkarpurapud_1-1751609746480.png


Thank you for being part of Fabric Community Forum.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

mromain
Regular Visitor

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

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.