Wide format to long format with multiple columns

Hi, Anonymous ,

You could merge multiple columns then unpivot,such as:

1.merge column

2.unpovit it.

3.split it.

The final show:

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRciwoUADRQYmZeUn55UCWoZGxgi+QpxBcAuRZggCQds7JL02ByhoWlyAkLZRidUCGGUEMA1FumUXFJVC15iUZCo5lqUCeBQgA6eDU5Pw8uFEGKPKWSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Applicant ID" = _t, #"Applicant Name" = _t, #"Site Name 1" = _t, #"Site Address 1" = _t, #"Site Zip 1" = _t, #"Site Name 2" = _t, #"Site Address 2" = _t, #"Site Zip 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Applicant ID", Int64.Type}, {"Applicant Name", type text}, {"Site Name 1", type text}, {"Site Address 1", type text}, {"Site Zip 1", Int64.Type}, {"Site Name 2", type text}, {"Site Address 2", type text}, {"Site Zip 2", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Site Zip 1", type text}}, "en-US"),{"Site Name 1", "Site Address 1", "Site Zip 1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Site Zip 2", type text}}, "en-US"),{"Site Name 2", "Site Address 2", "Site Zip 2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged.1"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Applicant ID", "Applicant Name"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Value.1", "Site Name"}, {"Value.2", "Site Address"}, {"Value.3", "Site Zip"}})
in
    #"Renamed Columns"

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