Row Population and Transposing of data

[ibesmond]

Hello

I have a data set that has some problems and needs cleanup, but I'm not sure how to accomplish this feat.

Basically I want to flatten the data as much as possible without loosing any data.
The data came from different sources so each row represents a row, and the column is where the data is located.
If This was a small set I would just go manually, but this is only a small subset of the data.
The sources are in no particular order.

The first data set is problem.
The second data set is solution 1. If I could get the data to this position I would be happy, but
The third tab is solution 2, if I could get it to this stage that would be the ultimate goal.

Name	Genre	Born Date	Died Date	Born Location	Died Location	Parents	Favorite Songs	Cause of death	Religion	Movies
Bob Marley	Reggae
Bob Marley								Melanoma	Rastafari	Marley
Bob Marley						Norval Marley, Cedella Booker	Corner Stone
Bob Marley		2/6/1945​	5/11/1981​	Nine Mile, Jamaica	Miami, FL
Bob Marley							Redemption Song
Buju Banton	Reggae
Buju Banton		7/15/1973​		Kingston, Jamaica
Buju Banton							Untold Stories
Bunny Wailer	Reggae
Bunny Wailer		4/10/1947​		Kingston, Jamaica
Bunny Wailer							Cool Runnings
Burning Spear	Reggae						Old Marcus Garvey
Burning Spear		3/1/1945​		St. Ann Parish, Jamaica
Damian Marley	Reggae
Damian Marley		7/21/1978​		Kingston, Jamaica
Damian Marley						Bob Marley, Cindy Breakspeare	Welcome to Jam Rock
Damian Marley							Medication
Dennis Brown
Dennis Brown	Reggae							Pneumothorax
Dennis Brown							The Promised Land
Dennis Brown		2/1/1957​	7/1/1999​	Kingston, Jamaica	Kingston, Jamaica
Desmend Dekker	Reggae
Desmend Dekker		7/16/1941​	5/25/2006​	St. Andrew Parish, Jamaica	Thornton Heath, United Kingdom
Desmend Dekker							Isrealites
Jimmy Cliff	Reggae
Jimmy Cliff		4/1/1948​		St. James Parish, Jamaica
Jimmy Cliff							I can See Clearly Now
Jimmy Cliff
Lee "Scratch" Perry	Reggae
Lee "Scratch" Perry		3/20/1936​		Kendal, Jamaica
Lee "Scratch" Perry							Chase the Devil			Lee Scratch Perry's Vision of Paradise
Lee "Scratch" Perry
Peter Tosh	Reggae
Peter Tosh							You Can't Blame the Youth
Peter Tosh		10/19/1944​	9/11/1987​	Grange Hill, Jamaica	Kingston, Jamaica
Peter Tosh								Murder	Rastafari	Stepping Razor: Red X
Peter Tosh							Legalize it
Peter Tosh							Glass House
Peter Tosh
Stephen Marley	Reggae
Stephen Marley		5/19/1972​		Wilmington, DE
Stephen Marley							Mind Control
Stephen Marley							Medication
Stephen Marley							Chase Dem
Stephen Marley							Iron Bars
Stephen Marley							Inna Di Red
Ziggy Marley	Reggae
Ziggy Marley		10/17/1968​		Kingston, Jamaica
Ziggy Marley							Looking
Ziggy Marley							True to Myself
Ziggy Marley							Dragonfly
Ziggy Marley							Keep My Faith
Ziggy Marley										Shark Tale
Ziggy Marley										Life and Debt
Ziggy Marley										I Am Bolt
Ziggy Marley										Pup Star
Ziggy Marley										The Strink is In

2nd Set

Name	Genre	Born Date	Died Date	Born Location	Died Location	Parents	Favorite Songs	Cause of death	Religion	Movies
Bob Marley	Reggae	2/6/1945​	5/11/1981​	Nine Mile, Jamaica	Miami, FL	Norval Marley, Cedella Booker	Corner Stone	Melanoma	Rastafari	Marley
Bob Marley	Reggae	2/6/1945​	5/11/1981​	Nine Mile, Jamaica	Miami, FL	Norval Marley, Cedella Booker	Redemption Song	Melanoma	Rastafari	Marley
Buju Banton	Reggae	7/15/1973​		Kingston, Jamaica			Untold Stories
Bunny Wailer	Reggae	4/10/1947​		Kingston, Jamaica			Cool Runnings
Burning Spear	Reggae	3/1/1945​		St. Ann Parish, Jamaica			Old Marcus Garvey
Damian Marley	Reggae	7/21/1978​		Kingston, Jamaica		Bob Marley, Cindy Breakspeare	Welcome to Jam Rock
Damian Marley	Reggae	7/21/1978​		Kingston, Jamaica		Bob Marley, Cindy Breakspeare	Medication
Dennis Brown	Reggae	2/1/1957​	7/1/1999​	Kingston, Jamaica	Kingston, Jamaica		The Promised Land	Pneumothorax
Desmend Dekker	Reggae	7/16/1941​	5/25/2006​	St. Andrew Parish, Jamaica	Thornton Heath, United Kingdom		Isrealites
Jimmy Cliff	Reggae	4/1/1948​		St. James Parish, Jamaica			I can See Clearly Now
Lee "Scratch" Perry	Reggae	3/20/1936​		Kendal, Jamaica			Chase the Devil			Lee Scratch Perry's Vision of Paradise
Peter Tosh	Reggae	10/19/1944​	9/11/1987​	Grange Hill, Jamaica	Kingston, Jamaica		You Can't Blame the Youth	Murder	Rastafari	Stepping Razor: Red X
Peter Tosh	Reggae	10/19/1944​	9/11/1987​	Grange Hill, Jamaica	Kingston, Jamaica		Legalize it	Murder	Rastafari	Stepping Razor: Red X
Peter Tosh	Reggae	10/19/1944​	9/11/1987​	Grange Hill, Jamaica	Kingston, Jamaica		Glass House	Murder	Rastafari	Stepping Razor: Red X
Stephen Marley	Reggae	5/19/1972​		Wilmington, DE			Mind Control
Stephen Marley	Reggae	5/19/1972​		Wilmington, DE			Medication
Stephen Marley	Reggae	5/19/1972​		Wilmington, DE			Chase Dem
Stephen Marley	Reggae	5/19/1972​		Wilmington, DE			Iron Bars
Stephen Marley	Reggae	5/19/1972​		Wilmington, DE			Inna Di Red
Ziggy Marley	Reggae	10/17/1968​		Kingston, Jamaica			Looking			Shark Tale
Ziggy Marley	Reggae	10/17/1968​		Kingston, Jamaica			True to Myself			Life and Debt
Ziggy Marley	Reggae	10/17/1968​		Kingston, Jamaica			Dragonfly			I Am Bolt
Ziggy Marley	Reggae	10/17/1968​		Kingston, Jamaica			Keep My Faith			Pup Star
Ziggy Marley	Reggae	10/17/1968​		Kingston, Jamaica			Keep My Faith			The Strink is In

3rd Set

Name

Genre

Born Date

Died Date

Born Location

Died Location

Parents

Favorite Songs1

Favorite Songs2

Favorite Songs3

Favorite Songs4

Favorite Songs5

Cause of death

Religion

Movies1

Movies12

Movies3

Movies4

Movies5

Bob Marley

Reggae

2/6/1945​

5/11/1981​

Nine Mile, Jamaica

Miami, FL

Norval Marley, Cedella Booker

Corner Stone

Redemption Song

Melanoma

Rastafari

Marley

Buju Banton

Reggae

7/15/1973​

Kingston, Jamaica

Untold Stories

Bunny Wailer

Reggae

4/10/1947​

Kingston, Jamaica

Cool Runnings

Burning Spear

Reggae

3/1/1945​

St. Ann Parish, Jamaica

Old Marcus Garvey

Damian Marley

Reggae

7/21/1978​

Kingston, Jamaica

Bob Marley, Cindy Breakspeare

Welcome to Jam Rock

Medication

Dennis Brown

Reggae

2/1/1957​

7/1/1999​

Kingston, Jamaica

Kingston, Jamaica

The Promised Land

Pneumothorax

Desmend Dekker

Reggae

7/16/1941​

5/25/2006​

St. Andrew Parish, Jamaica

Thornton Heath, United Kingdom

Isrealites

Jimmy Cliff

Reggae

4/1/1948​

St. James Parish, Jamaica

I can See Clearly Now

Marley

Lee "Scratch" Perry

Reggae

3/20/1936​

Kendal, Jamaica

Chase the Devil

Lee Scratch Perry's Vision of Paradise

Peter Tosh

Reggae

10/19/1944​

9/11/1987​

Grange Hill, Jamaica

Kingston, Jamaica

You Can't Blame the Youth

Legalize it

Glass House

Murder

Rastafari

Stepping Razor: Red X

Stephen Marley

Reggae

5/19/1972​

Wilmington, DE

Mind Control

Medication

Chase Dem

Iron Bars

Inna Di Red

Ziggy Marley

Reggae

10/17/1968​

Kingston, Jamaica

Looking

True to Myself

Dragonfly

Keep My Faith

Shark Tale

Life and Debt

I Am Bolt

Pup Star

The Strink is In

I appreciate the advise. Thank you

 

[sandy666]

oops, I c you can't do that

 

[lrobbo314]

You can make it a bit shorter with count delimiter and maxcount

SplitSongs = Table.SplitColumn(Group, "Favorite Songs", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), MaxCount),

This is a cool tip

 

[sandy666]

maybe somehow you will do that inside group or another way. good luck

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {
        {"Genre", each Text.Combine(_[Genre],", "), type text},
        {"Born Date", each Text.Combine(_[Born Date],", "), type text},
        {"Died Date", each Text.Combine(_[Died Date],", "), type text},
        {"Born Location", each Text.Combine(_[Born Location],", "), type text},
        {"Died Location", each Text.Combine(_[Died Location],", "), type text},
        {"Parents", each Text.Combine(_[Parents],", "), type text},
        {"Favorite Songs", each Text.Combine(_[Favorite Songs],", "), type text},
        {"Cause of death", each Text.Combine(_[Cause of death],", "), type text},
        {"Religion", each Text.Combine(_[Religion],", "), type text},
        {"Movies", each Text.Combine(_[Movies],", "), type text}
        }),
    MaxCountSongs = List.Max(Table.AddColumn(Group, "SCount", each List.Count(Text.Split([Favorite Songs],",")))[SCount]),
    MaxCountMovies = List.Max(Table.AddColumn(Group, "SCount", each List.Count(Text.Split([Movies],",")))[SCount]),
    SplitSongs = Table.SplitColumn(Group, "Favorite Songs", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), MaxCountSongs),
    SplitMovies = Table.SplitColumn(SplitSongs, "Movies", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), MaxCountMovies)
in
    SplitMovies