Help Conversion of information contained in many lines into one line for 1 identifier

Hi Luzadriana255 ,

Shravan133 's reply is great and vaild.

Here's my solution with .pbix file attached.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZC7DoIwFIZfpemshFKssOngJTGOLhKGqgck9JKUMvhubr6YiPFg0K39/vT/Tk+WUUEnlOaT9+FgPDgDVw2GMKTLk4dKtaYkHNn2cS9BNd4FRKRId1Z3T710SEQYc07WTpq6aJ3vefJRJiNl9BsfOzOZ4VWEnH3VEanJXlYG87oOykWpZaWCs9U9Tj916cjGka7UCbqvyKYBEieIO1mUjmbHNhaO6uIBDwsTA9y0XkHrXyubTxkbgn+aPqhMYRc3ebU2uADN8yc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Identifikator = _t, Information = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Identifikator", Int64.Type}, {"Information", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Information] <> "")),
    //Rows where the Information column is empty are filtered out.
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([Information], "@")),
    //Rows where the Information column contains “@” are filtered out.
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", each if Text.Contains([Information], "Zeil ") or Text.Contains([Information], "Hügelstr") or Text.Contains([Information], "Elbestrasse") or Text.Contains([Information], "Gutleutstr") then "Address"

else if Text.Contains([Information], "603") or Text.Contains([Information], "604") then "ZIP Code"

else if Text.Contains([Information], "Unternehmen") or Text.Contains([Information], "Abteilung") then "Company"

else null),
//A new column Custom is added based on the content of the Information column:
//If Information contains certain street names, it is labeled as “Address”.
//If Information contains certain ZIP codes, it is labeled as “ZIP Code”.
//If Information contains certain company-related terms, it is labeled as “Company”.
//Otherwise, it is labeled as null.
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Group(#"Added Custom", {"Custom","Identifikator"}, {{"Information", each Text.Combine([Information], "-"), type text}})),
    //Another custom column is added, grouping the table by Custom and Identifikator, and combining the Information values into a single string separated by “-”.
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Information"}, {"Information.1"}),
    //The grouped table is expanded back into the main table, adding a new column Information.1.
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.1", each Text.Contains([Information.1],[Information])),
    //A new column Custom.1 is added, checking if Information.1 contains the original Information value.
    #"Filtered Rows2" = Table.SelectRows(#"Added Custom2", each ([Custom.1] = true)),
    //Rows where Custom.1 is true are kept.
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Information"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Filtered Rows3" = Table.SelectRows(#"Removed Duplicates", each ([Custom] <> null)),
    //Rows where Custom is not null are kept.
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows3",{"Custom.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom]), "Custom", "Information.1")
    //The table is pivoted, using the distinct values in the Custom column as new columns, and the Information.1 values as the data.
in
    #"Pivoted Column"

Current table:

Final result:

Best Regards,

Stephen Tao

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