Transform data which has multiple columns with the same name but different values.

[Anonymous]

Below is the data, which I have multiple cash and credit columns. 

Date	Salesman Name	Branch	Item E1	Item E2	Item E3	Item E4	CASH	CREDIT	Item S1	Item S2	Item S3	CASH	CREDIT
01-10-2024	a	X	3	0	2	6	20	30	5	2	34	43	30
01-10-2024	b	S	12	1	4	4	16	24	78	243	34	43	21
01-10-2024	c	A	5	15	0	9	19	12	9	0	65	20	14

I want to transform the data so that I can  have only one cash and credit column obtain this:-

Date	Salesman Name	Branch	Items	Quantity	Cash	Credit
01-10-2024	a	X	Item E1	3	20	30
01-10-2024	a	X	Item E2	0	20	30
01-10-2024	a	X	Item E3	2	20	30
01-10-2024	a	X	Item E4	6	20	30
01-10-2024	a	X	Item S1	5	43	30
01-10-2024	a	X	Item S2	2	43	30
01-10-2024	a	X	Item S3	34	43	30
01-10-2024	b	S	Item E1	12	16	24
01-10-2024	b	S	Item E2	1	16	24
01-10-2024	b	S	Item E3	4	16	24
01-10-2024	b	S	Item E4	4	16	24
01-10-2024	b	S	Item S1	78	43	21
01-10-2024	b	S	Item S2	243	43	21
01-10-2024	b	S	Item S3	34	43	21
01-10-2024	c	A	Item E1	5	19	12
01-10-2024	c	A	Item E2	15	19	12
01-10-2024	c	A	Item E3	0	19	12
01-10-2024	c	A	Item E4	9	19	12
01-10-2024	c	A	Item S1	9	20	14
01-10-2024	c	A	Item S2	0	20	14
01-10-2024	c	A	Item S3	65	20	14

[slorin]

@Anonymous 

let
Source = YourSource,
ToRows = Table.ToRows(Source),
First_Row = ToRows{0},
Data = List.Skip(ToRows),
CASH_position = List.PositionOf(First_Row,"CASH",Occurrence.All),
Supervisor_position = List.PositionOf(First_Row,"Supervisor Name",Occurrence.First),
CREDIT_position = List.PositionOf(First_Row, "CREDIT", Occurrence.All),
#"Item position" = List.Transform(
  List.Zip({
    List.Transform(List.RemoveLastN({Supervisor_position} & CREDIT_position,1), each _+1),
    List.Transform(CASH_position, each _-1)}),
  each {_{0}.._{1}}),
TransformMany = List.TransformMany(
   Data,
   each #"Item position",
   (x,y) => List.FirstN(x, Supervisor_position+1) &
      {Table.FromRows(List.Transform(y, each {First_Row{_}, x{_}}), {"Items", "Quantity"})} &
      {x{List.Max(y)+1}} & {x{List.Max(y)+2}} ),
FromRows = Table.FromRows(TransformMany, List.FirstN(First_Row, Supervisor_position+1) & {"Data"} & List.LastN(First_Row, 2)),
Expand = Table.ExpandTableColumn(FromRows, "Data", {"Items", "Quantity"}, {"Items", "Quantity"})
in
Expand

Stéphane 

[slorin]

Hi @Anonymous 

Branch --> Area 

#"Branch and CREDIT position" = List.PositionOfAny(Record.ToList(Source{0}), {"Area","CREDIT"}, Occurrence.All),

 Stéphane

[slorin · Accepted solution]

Hi @Anonymous 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZU8xDoMwDPwKyoxEnARaRlqQytKh6VAJMaQoEkNhaPm/6qMhUsVw9tk5n52uE7VbvEiFdS//mdycXN2E+vR28zAyaRc/JQ1FpiLTkRlm58pekG5N3d63FxvnbJyzeqfu005IykhmSiqYOcaDAalkYLhARqER8tDVkBv9a+98nvgZgyDFLSaAVjeww3Fl+s9L0d5rYFRhM+XhsBJFuW0oQ7fIt1vJiL7/Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Colonne 1" = _t, #"Colonne 2" = _t, #"Colonne 3" = _t, #"Colonne 4" = _t, #"Colonne 5" = _t, #"Colonne 6" = _t, #"Colonne 7" = _t, #"Colonne 8" = _t, #"Colonne 9" = _t, #"Colonne 10" = _t, #"Colonne 11" = _t, #"Colonne 12" = _t, #"Colonne 13" = _t, #"Colonne 14" = _t]),
#"CASH position" = List.PositionOf(Record.ToList(Source{0}),"CASH",Occurrence.All),
#"Branch and CREDIT position" = List.PositionOfAny(Record.ToList(Source{0}), {"Branch","CREDIT"}, Occurrence.All),
#"Item position" = List.Transform(
   List.Zip({
     List.Transform(List.RemoveLastN(#"Branch and CREDIT position",1), each _+1),
     List.Transform(#"CASH position", each _-1)}),
   each {_{0}.._{1}}),
ToRows = Table.ToRows(Source),
#"First Row" = ToRows{0},
Data = List.Skip(ToRows),
TransformMany = List.TransformMany(
   Data,
   each #"Item position",
   (x,y) => List.FirstN(x,3) &
           {Table.FromRows(List.Transform(y, each {#"First Row"{_}, x{_}}), {"Items", "Quantity"})} &
           {x{List.Max(y)+1}} & {x{List.Max(y)+2}} ),
FromRows = Table.FromRows(TransformMany, List.FirstN(#"First Row", 3) & {"Data"} & List.LastN(#"First Row", 2)),
Expand = Table.ExpandTableColumn(FromRows, "Data", {"Items", "Quantity"}, {"Items", "Quantity"})
in
Expand

Stephane

[Anonymous]

I tried to implement this into my data but I'm facing an error.
Expression.Error: We cannot apply operator .. to types Null and Number.

I have attached the file.

https://docs.google.com/spreadsheets/d/1hFQ8lTrGRyIV-vsDgOwJyolKP1lTLzoC/edit?usp=sharing&ouid=10407... 

[audreygerred]

Hi! In Power Query I would make two separate tables. One, a table that has these columns:

Date

Salesman Name

Branch

Item E1

Item E2

Item E3

Item E4

CASH

CREDIT

Then, unpivot it so that you wind up with 

Date Salesman Name Branch Item Cash Credit

Next, create a table that Date Salesman Branch, your S items, cash and credit and unpivot that one as well. Make sure the field names are identical in both tables.

Append table 2 to table 1. Disable load on table 2 since you do not need it showing as a separate table in Power BI.