Forum Discussion

WinterGarden's avatar
WinterGarden
Resolver I
1 year ago
Solved

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

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:

FiscalYearMonthFiscalYear-MonthUDN
FY25P03FY25 P03690540356
FY25P03FY25 P03645311101
FY25P03FY25 P03462004128
FY25P03FY25 P03462004128
FY25P03FY25 P03645311101
FY25P04FY25 P04690540356
FY25P04FY25 P04645311101
FY25P04FY25 P041367960
FY25P04FY25 P041367960
FY25P04FY25 P04001367960
FY25P04FY25 P04462004128
FY25P04FY25 P04462004128
FY25P04FY25 P04462004128
FY25P04FY25 P04057156663


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

FiscalYearMonthFiscalYear-MonthUDN
FY25P03FY25 P03690540356
FY25P03FY25 P03645311101
FY25P03FY25 P03462004128
FY25P04FY25 P04690540356
FY25P04FY25 P04645311101
FY25P04FY25 P041367960
FY25P04FY25 P04462004128
FY25P04FY25 P04057156663


How can i achieve that in edit query?

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

5 Replies

    • WinterGarden's avatar
      WinterGarden
      Resolver I
      1 year ago

      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's avatar
        FreemanZ
        Super User
        1 year ago

        hi WinterGarden ,

         

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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