Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Need Help Importing Data and Changing Table in Power Query

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)....
Need Help
  • Mahesh0016's avatar
    Mahesh0016
    3 years ago

    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's avatar
Einomi
Helper V
3 years ago

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"