Wide format to long format with multiple columns

[Anonymous]

Hello,

I need to convert data from one applicant per row (with up to 15 sites) to one site per row, ie, from wide to long format. I saw a solution to use unpivot, but I'm having a hard time figuring out how to do that when multiple columns need to be unpivoted. Here is an example of what my data looks like: 

Current state:

Applicant ID	Applicant Name	Site Name 1	Site Address 1	Site Zip 1	Site Name 2	Site Address 2	Site Zip 2
001	App 1	Rainbow	123 Main St	99999	Cloud	123 1st St	99998
002	App2	First	123 7th Ave	88888	Second	123 10th Ave	88889

Desired output:

Applicant ID	Applicant Name	Site Name	Site Address	Site Zip
001	App 1	Rainbow	123 Main St	99999
001	App 1	Cloud	123 1st St	99998
002	App2	First	123 7th Ave	88888
002	App2	Second	123 10th Ave	88889

Thank you!

[v-yalanwu-msft · Accepted solution]

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.