Need Help Importing Data and Changing Table in Power Query

[Anonymous]

I have an excel file of participants of a pension fonds. The following columns are available:

- UniqueID
- policynumber

- Group

The Group can either be Active (A), Sleeper (S) or Retired (R).
Here is the thing, some participants can have multiple policynumbers with different Group.
Example: A participant can be both Active and Retired at the same time.

UniqueID	PolicyNumber	Group
XYZ1234	1	A
XYZ1234	2	R
XYZ0001	3	A
XYZ0002	4	R
XYZ0002	5	R
XYZ0003	6	A
XYZ0003	7	S
XYZ0003	8	R

I need to transfer the table above to the following table:

UniqueID	PolicyNumber	Group
XYZ1234	2	AR
XYZ0001	3	A
XYZ0002	5	R
XYZ0003	8	ASR

The table should not have any double UniqueID's.
In case of multiple PolicyNumbers, the max is transfered.
In case of Multiple Groups, They are Concatonated together.

Can anyone help me realize this?

I'm not sure if this should be done either in Power Query, or using DAX after import.
Either case would help as long as I have the same outcome.

With kind regards,

LaZZaNoVA

[Mahesh0016 · Accepted solution]

@Anonymous 

Use below M Code you don't need duplicate Query and merge Query. you get one query in result.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMMjQyNlHSUTIEYkelWB1kMSMgDoKJGRgYgNQYI6sDioHUmKCpA4mZoomB9Jmh6QWJmQNxMJqYBURvLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UniqueID = _t, PolicyNumber = _t, Group = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", type text}, {"PolicyNumber", Int64.Type}, {"Group", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"UniqueID"}, {{"PolicyNumber", each List.Max([PolicyNumber]), Int64.Type},{"Group",each Text.Combine(List.Distinct([Group])), type text}})
in
#"Grouped Rows"

** If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.

[Einomi]

Hi,

I have uploaded your table to PQ. Then I duplicated it, then merge the two. I will paste the two M codes

I duplicated the original query

Then the second query I have done the following modifications

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau2"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"PolicyNumber", Int64.Type}}),
    #"Lignes groupées" = Table.Group(#"Type modifié", {"UniqueID"}, {{"Groupp", each Text.Combine([Group],""), type nullable text}})
in
    #"Lignes groupées"

then I merge the two

let
    Source = Table.NestedJoin(Tableau2, {"UniqueID"}, #"Tableau2 (2)", {"UniqueID"}, "Tableau2 (2)", JoinKind.LeftOuter),
    #"Tableau2 (2) développé" = Table.ExpandTableColumn(Source, "Tableau2 (2)", {"PolicyNumber", "Groupp"}, {"PolicyNumber", "Groupp.1"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Tableau2 (2) développé",{"Groupp.1"}),
    #"Colonnes permutées" = Table.ReorderColumns(#"Colonnes supprimées",{"UniqueID", "PolicyNumber", "Groupp"}),
    #"Lignes groupées" = Table.Group(#"Colonnes permutées", {"UniqueID", "Groupp"}, {{"PN_Max", each List.Max([PolicyNumber]), type nullable number}})
in
    #"Lignes groupées"