What is the quick way to transfer data from row to column

Sian1 · Feb 26, 2020



Request ID	101	102	104	105	116	134	135	143	147	148	149
R-042A04GC700000											149
R-023A0DAB800000											149
R-042A059Q100000								143			149
R-042A059Q100000
R-042A059Q100000
R-005A0HFX800001		102		105							149
R-042A059Y400000			104			134					149
R-042A059Y400000											149
R-023A0BE0200000											149
R-001A0Y3T700000						134	135
R-001A0Y3T700000					116			143	147		149
R-023A0BE0200000	101

Sian1 · Feb 26, 2020

end results



Request ID	Rework Code
R-042A04GC700000	149
R-042A059Q100000	129
R-042A059Q100000	143
R-042A059Q100000	149

snipescc · Feb 26, 2020

It's unclear exactly what you are asking for. Can you explain with more detail?

Sian1 · Feb 26, 2020

snipescc said:
It's unclear exactly what you are asking for. Can you explain with more detail?

Hi, what im trying to do is rearrange the data. as you see below table, the Request ID will still remain the same, however I would like the return the Row value (header) into the column. Hope you could help

lrobbo314 · Feb 26, 2020

Something like this?

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

Request ID

101

102

104

105

116

134

135

143

147

148

149

Request ID

Count

Code

2

R-042A04GC700000

149

R-042A04GC700000

1

149

3

R-023A0DAB800000

149

R-023A0DAB800000

1

149

4

R-042A059Q100000

143

149

R-042A059Q100000

2

143149

5

R-042A059Q100000

R-005A0HFX800001

3

102105149

6

R-042A059Q100000

R-042A059Y400000

4

1.04134E+11

7

R-005A0HFX800001

102

105

149

R-023A0BE0200000

2

149101

8

R-042A059Y400000

104

134

149

R-001A0Y3T700000

6

1.34135E+17

9

R-042A059Y400000

149

10

R-023A0BE0200000

149

11

R-001A0Y3T700000

134

135

12

R-001A0Y3T700000

116

143

147

149

13

R-023A0BE0200000

101

Sheet1

Power Query Code

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Request ID"}, "Attribute", "Value"),
    Remove = Table.RemoveColumns(Unpivot,{"Attribute"}),
    Group = Table.Group(Remove, {"Request ID"}, {{"Count", each _, type table}}),
    Combo = Table.AddColumn(Group, "Code", each Text.Combine(List.Transform(Table.Column([Count],"Value"),Text.From),",")),
    Count = Table.TransformColumns(Combo,{"Count", each Table.RowCount(_)})
in
    Count

lrobbo314 · Feb 26, 2020

I don't know why the 'Code' column is coming out like that on the forum. It's supposed to be comma separated strings of all the codes the request ID had attributed to it.

MARK858 · Feb 26, 2020

lrobbo314 said:
I don't know why the 'Code' column is coming out like that on the forum. It's supposed to be comma separated strings of all the codes the request ID had attributed to it.

@lrobbo314, What version of XL2BB are you using as I get the below result...
It looks like version v1.0 whereas the current version is v1.2.6

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Request ID

101

102

104

105

116

134

135

143

147

148

149

Request ID

Count

Code

2

R-042A04GC700000

149

R-042A04GC700000

1

149

3

R-023A0DAB800000

149

R-023A0DAB800000

1

149

4

R-042A059Q100000

143

149

R-042A059Q100000

2

143,149

5

R-042A059Q100000

R-005A0HFX800001

3

102,105,149

6

R-042A059Q100000

R-042A059Y400000

4

104,134,149,149

7

R-005A0HFX800001

102

105

149

R-023A0BE0200000

2

149,101

8

R-042A059Y400000

104

134

149

R-001A0Y3T700000

6

134,135,116,143,147,149

9

R-042A059Y400000

149

10

R-023A0BE0200000

149

11

R-001A0Y3T700000

134

135

12

R-001A0Y3T700000

116

143

147

149

13

R-023A0BE0200000

101

Sheet5

Sian1 · Feb 26, 2020

lrobbo314 said:
Something like this?

Book1
A B C D E F G H I J K L M N O P
1 Request ID 101 102 104 105 116 134 135 143 147 148 149 Request ID Count Code
2 R-042A04GC700000 149 R-042A04GC700000 1 149
3 R-023A0DAB800000 149 R-023A0DAB800000 1 149
4 R-042A059Q100000 143 149 R-042A059Q100000 2 143149
5 R-042A059Q100000 R-005A0HFX800001 3 102105149
6 R-042A059Q100000 R-042A059Y400000 4 1.04134E+11
7 R-005A0HFX800001 102 105 149 R-023A0BE0200000 2 149101
8 R-042A059Y400000 104 134 149 R-001A0Y3T700000 6 1.34135E+17
9 R-042A059Y400000 149
10 R-023A0BE0200000 149
11 R-001A0Y3T700000 134 135
12 R-001A0Y3T700000 116 143 147 149
13 R-023A0BE0200000 101
Sheet1

Power Query Code

Code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Unpivot = Table.UnpivotOtherColumns(Source, {"Request ID"}, "Attribute", "Value"), Remove = Table.RemoveColumns(Unpivot,{"Attribute"}), Group = Table.Group(Remove, {"Request ID"}, {{"Count", each _, type table}}), Combo = Table.AddColumn(Group, "Code", each Text.Combine(List.Transform(Table.Column([Count],"Value"),Text.From),",")), Count = Table.TransformColumns(Combo,{"Count", each Table.RowCount(_)}) in Count

hi, thanks

it is close but i am more looking for the Unique code as the Power BI could distinct count the Request ID. I would like the return results like the table in the 2nd post.

Sian1 · Feb 26, 2020

MARK858 said:
@lrobbo314, What version of XL2BB are you using as I get the below result...
It looks like version v1.0 whereas the current version is v1.2.6

Book1
A B C D E F G H I J K L M N O P Q
1 Request ID 101 102 104 105 116 134 135 143 147 148 149 Request ID Count Code
2 R-042A04GC700000 149 R-042A04GC700000 1 149
3 R-023A0DAB800000 149 R-023A0DAB800000 1 149
4 R-042A059Q100000 143 149 R-042A059Q100000 2 143,149
5 R-042A059Q100000 R-005A0HFX800001 3 102,105,149
6 R-042A059Q100000 R-042A059Y400000 4 104,134,149,149
7 R-005A0HFX800001 102 105 149 R-023A0BE0200000 2 149,101
8 R-042A059Y400000 104 134 149 R-001A0Y3T700000 6 134,135,116,143,147,149
9 R-042A059Y400000 149
10 R-023A0BE0200000 149
11 R-001A0Y3T700000 134 135
12 R-001A0Y3T700000 116 143 147 149
13 R-023A0BE0200000 101
Sheet5

hi, thanks

it is close but i am more looking for the Unique code as the Power BI could distinct count the Request ID. I would like the return results like the table in the 2nd post. do you have any suggestions?

lrobbo314 · Feb 26, 2020

So, just this then?

Book1 (version 2).xlsb

A

B

1

Request ID

Code

2

R-042A04GC700000

149

3

R-023A0DAB800000

149

4

R-042A059Q100000

143

5

R-042A059Q100000

149

6

R-005A0HFX800001

102

7

R-005A0HFX800001

105

8

R-005A0HFX800001

149

9

R-042A059Y400000

104

10

R-042A059Y400000

134

11

R-042A059Y400000

149

12

R-042A059Y400000

149

13

R-023A0BE0200000

149

14

R-001A0Y3T700000

134

15

R-001A0Y3T700000

135

16

R-001A0Y3T700000

116

17

R-001A0Y3T700000

143

18

R-001A0Y3T700000

147

19

R-001A0Y3T700000

149

20

R-023A0BE0200000

101

Sheet12

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    unpivot = Table.UnpivotOtherColumns(Source, {"Request ID"}, "Code", "Value"),
    selectColumns = Table.SelectColumns(unpivot,{"Request ID", "Code"}),
    Type = Table.TransformColumnTypes(selectColumns,{{"Request ID", type text}, {"Code", Int64.Type}})
in
    Type

What is the quick way to transfer data from row to column

Sian1

Board Regular

Excel Facts

Sian1

Board Regular

snipescc

Board Regular

Sian1

Board Regular

Attachments

lrobbo314

Well-known Member

lrobbo314

Well-known Member

MARK858

MrExcel MVP

Sian1

Board Regular

Sian1

Board Regular

lrobbo314

Well-known Member

Similar threads

Forum statistics

Share this page

What is the quick way to transfer data from row to column

Board Regular

Excel Facts

Board Regular

Board Regular

Board Regular

Attachments

Well-known Member

Well-known Member

MrExcel MVP

Board Regular

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock