Transposing Data

[Anonymous]

Hello, 

I am having trouble figuring this out.

How data is currently laid out in Excel:

Column A      Column B

155                10 Dwight Rd

155                15 Phyllis drive

1566              24 Stanley Road

1566              233 Pam & Jim Court

What I am trying to get data layout to be:

Column A      Column B                       Column C.......        Column (However many needed)

155               10 Dwight Rd                 15 Phyllis Drive

1566              24 Stanley Road            233 Pam & Jim Court

Column A is the key for each address and each key can have multiple addresses as shown above. Some have ten addresses others have as low as one. 

Thank you for any help you may give.

[Padycosmos]

In Power Query please do the following :

In the query window, delete everything and  paste the code below

= Table.Group(#"Changed Type", {"Column A"}, {{"Column B", each Text.Combine([Column B],","), type nullable text}})

Then rightclick on the above step in the applied steps pane >> Insert step After and  paste the code below:

= Table.SplitColumn(#"Custom1", "Column B", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column B.1", "Column B.2"})

[Anonymous]

Hi,

I am attempting. Each time I get an error stating " The name 'Changed Type' wasn't recognized"

[Padycosmos]

Instead of Changed Type, type the name of the last step . If you could send the screenshot of the applied steps I can help you

[amitchandak]

@Anonymous , Create a sub category index and then you can use that to pivot or create a matrix column

https://www.youtube.com/watch?v=7CqXdSEN2k4

then pivot

https://radacad.com/pivot-and-unpivot-with-power-bi

Or use Matrix visual

[Anonymous]

Thank you, I watched the first video and was able to follow and get to having a index column. I still don't understande the secdond part which actually trasposes this data. Any suggestions? 

[amitchandak]

@Anonymous , Please find the attached file. I have created two solutions (2 Tables)

Please open power query and check steps. Also check Matrix visual