Wide format to long format with multiple columns

Question

Hello,&nbsp;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:&nbsp;&nbsp;Current state:Applicant IDApplicant NameSite Name 1Site Address 1Site Zip 1Site Name 2Site Address 2Site Zip 2001App 1Rainbow123 Main St99999Cloud123 1st St99998002App2First123 7th Ave88888Second123 10th Ave88889&nbsp;Desired output:Applicant IDApplicant NameSite NameSite AddressSite Zip001App 1Rainbow123 Main St99999001App 1Cloud123 1st St99998002App2First123 7th Ave88888002App2Second123 10th Ave88889&nbsp;Thank you!

v-yalanwu-msft · Accepted Answer

Hi,&nbsp;Anonymous&nbsp;,
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 WuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Wide format to long format with multiple columns

1 Reply

Related content

Dynamical conditional formatting for multiple columns

Conditional Formatting a Matrix with multiple columns

Sequential Order column based on multiple criteria

Custom Conditional Formatting for Multiple Columns?

Multiple date formats in column

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

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