Wide format to long format with multiple columns

Anonymous — Mon, 04 Jul 2022 20:38:48 GMT

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!

Re: Wide format to long format with multiple columns

v-yalanwu-msft — Thu, 07 Jul 2022 07:38:15 GMT

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.

topic Wide format to long format with multiple columns in DAX Commands and Tips

Wide format to long format with multiple columns

Re: Wide format to long format with multiple columns