Power query issue, not sure how to implement this..

[WinterGarden]

Hi All,
 There is one sharepoint folder. In that folder i've added excel files corresponding to each month. Then i've loaded this folder in to the powerbi desktop and combined these in the edit query. Now the problem is that i wanted to remove duplicate values from each files. But if i apply remove duplicate it will remove all the existing duplicates except the one from first file
below is the screenshot of the combined table:

For example, Combined table will look like this:

FiscalYear	Month	FiscalYear-Month	UDN
FY25	P03	FY25 P03	690540356
FY25	P03	FY25 P03	645311101
FY25	P03	FY25 P03	462004128
FY25	P03	FY25 P03	462004128
FY25	P03	FY25 P03	645311101
FY25	P04	FY25 P04	690540356
FY25	P04	FY25 P04	645311101
FY25	P04	FY25 P04	1367960
FY25	P04	FY25 P04	1367960
FY25	P04	FY25 P04	001367960
FY25	P04	FY25 P04	462004128
FY25	P04	FY25 P04	462004128
FY25	P04	FY25 P04	462004128
FY25	P04	FY25 P04	057156663

my desired output is the table that we get after removing the duplicate udn from each month ie:

FiscalYear	Month	FiscalYear-Month	UDN
FY25	P03	FY25 P03	690540356
FY25	P03	FY25 P03	645311101
FY25	P03	FY25 P03	462004128
FY25	P04	FY25 P04	690540356
FY25	P04	FY25 P04	645311101
FY25	P04	FY25 P04	1367960
FY25	P04	FY25 P04	462004128
FY25	P04	FY25 P04	057156663

How can i achieve that in edit query?

[Anonymous · Accepted solution]

Hi @WinterGarden ,
You can ttry the following code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vdAxCoAwDAXQq0jmDkmT/NoLOLuW0vtfQ8VBEFoc1CUk8EjCr5WWEp0Crax7PYbpbJHZjdVBLQyUuYoIy1AZIrNJnF9QnYt2Ket/f1ePdokiZfAvppPBh8qTOACl1jY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FiscalYear = _t, Month = _t, #"FiscalYear-Month" = _t, UDN = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FiscalYear", type text}, {"Month", type text}, {"FiscalYear-Month", type text}, {"UDN", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"FiscalYear-Month", "UDN"}, {{"GroupTable", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table [FiscalYear=nullable text, Month=nullable text, #"FiscalYear-Month"=nullable text, UDN=nullable number, Index=Int64.Type]}}),
    #"Expanded GroupTable" = Table.ExpandTableColumn(#"Grouped Rows", "GroupTable", {"FiscalYear", "Month", "Index"}, {"FiscalYear", "Month", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded GroupTable", each ([Index] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[WinterGarden]

Hi @Anonymous,
Thank you so much 👍

[FreemanZ]

hi @WinterGarden ,

if you select all columns and remove duplicate rows, you shall get what you want. or?

[WinterGarden]

Hi @FreemanZ ,
i need to remove duplicate values for each month..
in the above sample data as you can see "462004128" in p03(twice) and p04(thrice)
i need to remove duplicates from p03 and p04..
so the output will have this udn in both p03 and p04 each

[FreemanZ]

hi @WinterGarden ,

i think i still dont get the point. Is the 2nd table in your original post what you expect?