Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Wide format to long format with multiple columns

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 IDApplicant NameSite Name 1Site Address 1Site Zip 1Site Name 2Site Address 2Site Zip 2
001App 1Rainbow123 Main St99999Cloud123 1st St99998
002App2First123 7th Ave88888Second123 10th Ave88889

 

Desired output:

Applicant IDApplicant NameSite NameSite AddressSite Zip
001App 1Rainbow123 Main St99999
001App 1Cloud123 1st St99998
002App2First123 7th Ave88888
002App2Second123 10th Ave88889

 

Thank you!

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ,

You could merge multiple columns then unpivot,such as:

1.merge column

vyalanwumsft_0-1657179257036.pngvyalanwumsft_1-1657179282843.png

2.unpovit it.

vyalanwumsft_2-1657179307797.png

3.split it.

vyalanwumsft_3-1657179334163.png

The final show:

vyalanwumsft_4-1657179348398.png

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.

View solution in original post

1 REPLY 1
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ,

You could merge multiple columns then unpivot,such as:

1.merge column

vyalanwumsft_0-1657179257036.pngvyalanwumsft_1-1657179282843.png

2.unpovit it.

vyalanwumsft_2-1657179307797.png

3.split it.

vyalanwumsft_3-1657179334163.png

The final show:

vyalanwumsft_4-1657179348398.png

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.