Clean up SQL import, record with multiple values in one column

[Pcuezze]

I imported some data from a SQL database. However, one field has multiple values. Each record is a row but in the field with multiple values, a 2nd row was created with only that value (column K). I would like to move data from K to the corresponding row above it in Column L, M, or N as needed. Although this picture shows each record containing exactly 4 Column K values, some have less. Normally, I'm pretty good at googling problems like this, but I couldn't figure out how to word my question correctly so thanks in advance! Patrick

 

[Pcuezze]

The insert image did not work, so let's try a hyperlink: http://www.kcmpcare.com/problem/

 

[Pcuezze]

Ok. Feeling dumb today. (sorry for the multiple posts, I'm to new to edit or delete my posts)

 

[sandy666]

much better is a link to the excel file than picture
is that what you want?

Column1

Column2

Column3

Column4

Column5

Column6

Column7

Column8

Column9

Column10

Custom.1

Custom.2

Custom.3

Custom.4

0​

jmonger

1004​

07/01/2018​

47​

LAKEVIEWLT

Bob

Smith

Female

8/23/33

G20

I10

N39.498

E78.5

0​

stownsley

1003​

07/01/2018​

45​

Nottingham

Bob

Smith

Male

9/25/52

C34.90

J15.9

K21.9

G89.29

0​

stownsley

1003​

07/01/2018​

44​

Nottingham

Bo

Smith

Male

4/1/32

R41.82

N39.0

N18.9

M62.81

0​

stownsley

1003​

07/01/2018​

45​

Nottingham

Bob

Smith

Male

5/19/46

I42.9

I25.1

F06.4

J44.1

0​

stownsley

1003​

07/01/2018​

44​

Nottingham

Bob

Smith

Female

8/30/38

K59.00

M13.0

M54.5

Ell.8

0​

stownsley

1003​

07/01/2018​

45​

Nottingham

Bob

Smith

Female

2/26/45

I25.1

K21.9

K59.00

M62.81

0​

stownsley

1003​

07/01/2018​

44​

Nottingham

Bob

Smith

Female

11/21/46

K59.00

S72.001A

N18.6

R41.82

0​

stownsley

1003​

07/01/2018​

44​

Nottingham

Bob

Smith

Male

5/10/35

R11.0

K21.9

M10.00

D64.9

0​

stownsley

1003​

07/01/2018​

45​

Nottingham

Bob

Smith

Male

12/25/39

M84.452A

 

[Pcuezze]

Yes!!! How'd you get there?

 

[sandy666]

take the data from sql db into PowerQuery (aka Get&Transform)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type date}, {"Column5", Int64.Type}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"Column11")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}, {"Custom.4", type text}})
in
    #"Changed Type1"[/SIZE]

 

[Pcuezze]

Awesome! Thank you so much.

 

[sandy666]

You are welcome

Have a nice day