Helper I

1 year ago

Solved

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.

Need Help

v-karpurapud

1 year ago

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

Thank you for being part of Fabric Community Forum.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

Filter.pbix51 KB

11 Replies

mromain

Regular Visitor

1 year ago

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
Helper I
1 year ago
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.

v-karpurapud

Community Support

1 year ago

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.

Filter.pbix39 KB

GFire

Helper I

1 year ago

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

Community Support

1 year ago

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.

Filter.pbix51 KB

v-karpurapud
Community Support
1 year ago
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
1 year ago
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.

Forum Discussion

Remove the date filter and show all columns

11 Replies

Related content

Removing date filter

Remove Blank Columns

Slicer for filtering several columns

Remove visual filter context

Remove filter on visual