Transform Data which has repeated column names but different values.

[Anonymous]

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

The above data is my sample data with the number of certain items and cash and credit columns twice. The above is the format in which I received the data.

Is it possible to categorize the items and transform the data like below?

Date	Salesman	Branch	Category	Cash	Credit
01-10-2024	a	X	E-Items	20	30
01-10-2024	b	S	E-Items	16	24
01-10-2024	c	A	E-Items	19	12
01-10-2024	a	X	S-Items	43	30
01-10-2024	b	S	S-Items	43	21
01-10-2024	c	A	S-Items	20	14

[PwerQueryKees]

I copy pasted you data in excel and loaded it in PowerQuery:

Applied a number of steps (all with the UI)

Resulting in the M code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}}),
    #"Filled Up" = Table.FillUp(#"Changed Type",{"Column1", "Column2", "Column3"}),
    #"Transposed Table" = Table.Transpose(#"Filled Up"),
    #"Removed Columns" = Table.RemoveColumns(#"Transposed Table",{"Column3"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date.Date", type text}, {"Salesman Name.Salesman Name", type text}, {"Branch.Branch", type text}, {"E-Items.Item E1", Int64.Type}, {"E-Items.Item E2", Int64.Type}, {"E-Items.Item E3", Int64.Type}, {"E-Items.Item E4", Int64.Type}, {"E-Items.CASH", Int64.Type}, {"E-Items.CREDIT", Int64.Type}, {"S Items.Item S1", Int64.Type}, {"S Items.Item S2", Int64.Type}, {"S Items.Item S3", Int64.Type}, {"S Items.CASH", Int64.Type}, {"S Items.CREDIT", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date.Date", "Date"}, {"Salesman Name.Salesman Name", "Salesman Name"}, {"Branch.Branch", "Branch"}})
in
    #"Renamed Columns"

Producing: 

[lbendlin · Accepted solution]

How is Power Query supposed to know what are "E-Items"  vs "S-Items" ?  Purely by their column order?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hU+xDoIwFPwV0hmSvragjCgksjhYBxPCUAkJgzAo/x/fYWk0DA7X3ru+d+/aNCIScUA992NUUWAqMB2YYXYs7AnXpSrr6/piw5wNc1Zvutu4EaWbe1ase/Sv0U3R2Y2oD083dcNXnv+Am6SEZKKkQjTHuDGwWDIQJcONQuNIvarRbvRH3vjckY9BaMXPjActbmC7/cL0j5eirVfHKPxmSn2wHEW+bsi9mqVrVjKibd8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t]),
    Headers = List.Skip(Record.ToList(Source{0}),3),
    #"Converted to Table" = Table.FromList(Headers, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Column1],each if [Column1]="CASH" then Text.At(#"Added Index"{[Index]-1}[Column1],5) & "-Items CASH" else if [Column1]="CREDIT" then Text.At(#"Added Index"{[Index]-2}[Column1],5) & "-Items CREDIT" else [Column1],Replacer.ReplaceValue,{"Column1"}),
    #"Removed Top Rows" = Table.Skip(Source,1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    NewHeaders = List.Zip({List.Skip(Table.ColumnNames(#"Promoted Headers"),3),#"Replaced Value"[Column1]}),
    Renamed = Table.RenameColumns(#"Promoted Headers",NewHeaders),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Renamed, {"Date", "Salesman Name", "Branch"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each Text.Contains([Attribute], "-")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Category", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"CASH", Currency.Type}, {"CREDIT", Currency.Type}})
in
    #"Changed Type"

[Anonymous]

Let's say that there is a merged row above 

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14
			E-Items						S Items
			Item E1	Item E2	Item E3	Item E4	CASH	CREDIT	Item S1	Item S2	Item S3	CASH	CREDIT
Date	Salesman Name	Branch
##########	a	X	3	0	2	6	20	30	5	2	34	43	30
##########	b	S	12	1	4	4	16	24	78	243	34	43	21
##########	c	A	5	15	0	9	19	12	9	0	65	20	14

[AlienSx]

let
    fx = (lst, positions) => List.Transform(positions, (i) => lst{i}),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cash = List.Buffer(List.PositionOf(Record.FieldValues(Source{1}), "CASH", Occurrence.All)),
    credit = List.Buffer(List.Transform(cash, (x) => x + 1)), 
    items = List.Select(Record.FieldValues(Source{0}), (x) => Text.Contains(x, "Items")), 
    trx = List.TransformMany(
        Table.ToRows(Table.Skip(Source, 3)),
        (x) => List.Zip({items, fx(x, cash), fx(x, credit)}),
        (x, y) => List.FirstN(x, 3) & y
    ), 
    result = Table.FromRows(trx, {"date", "salesman", "branch", "item", "cash", "credit"})
in
    result

[lbendlin]

The above is the format in which I received the data.

Is it or is it not?