The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to 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
Thank you for being part of Fabric Community Forum.
Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.
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.
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.
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.
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.
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.
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.