Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

replace Duplicate values for each source in the one query

Good day All, 

 

I have a query consists of varius sorce files. Some files have dublications. Is there is a way to exchage dublicated values within the query but for each source only, see below:

 

1 need only one 2nd officer in each sorce, only one 3rd officer within one sorce etc... 

 

Thnaks

Aleks 

Need Help
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi Anonymous ,

    Perhaps you can try:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1NFfSUTLKS1HwT0vLTE4tUorVQZbxKtJTMMYpa1yES8apKDMlPVUhJDUxF0nGEijjm1hcgqIcJOickZmahsUoS5yOs8RpPX4ZrA4zMsDiMLAgDoeB5bA6jIAMVoeBZVAcFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}, {"Value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Source"}, {{"Data", each _, type table [Source=nullable text, Value=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Distinct([Data])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value"}, {"Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", type text}})
in
    #"Changed Type1"

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    How to get good help fast. Help us help you.

    How To Ask A Technical Question If you Really Want An Answer

    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

  • v-yingjl's avatar
    v-yingjl
    Community Support
    4 years ago

    Hi Anonymous ,

    Perhaps you can try:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1NFfSUTLKS1HwT0vLTE4tUorVQZbxKtJTMMYpa1yES8apKDMlPVUhJDUxF0nGEijjm1hcgqIcJOickZmahsUoS5yOs8RpPX4ZrA4zMsDiMLAgDoeB5bA6jIAMVoeBZVAcFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}, {"Value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Source"}, {{"Data", each _, type table [Source=nullable text, Value=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Distinct([Data])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value"}, {"Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", type text}})
in
    #"Changed Type1"

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.