Power Query : Transpose To Multiple Column Based Criteria

[muhammad susanto]

hi all

how to transpose from multiple column to multiple column based on specific criteria or name
here this layout

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	original data					after using Power Query
2	Region	Model	Date	Amount
3	Hongkong	BMW	01/03/2022	10		BMW	01/03/2022	10	Mercy	01/03/2022	5
4	Hongkong	BMW	01/04/2022	10		BMW	01/04/2022	10	Mercy	01/04/2022	5
5	Hongkong	BMW	01/05/2022	10		BMW	01/05/2022	10	Mercy	01/05/2022	5
6	Hongkong	BMW	01/06/2022	10		BMW	01/06/2022	10	Mercy	01/06/2022	20
7	Hongkong	BMW	01/07/2022	10		BMW	01/07/2022	10	Mercy	01/07/2022	20
8	Hongkong	BMW	01/08/2022	10		BMW	01/08/2022	10	Mercy	01/08/2022	20
9	Hongkong	BMW	01/09/2022	10		BMW	01/09/2022	10	Mercy	01/09/2022	20
10	Hongkong	BMW	01/10/2022	0		BMW	01/10/2022	0	Mercy	01/10/2022	0
11	Hongkong	BMW	01/11/2022	0		BMW	01/11/2022	0	Mercy	01/11/2022	0
12	Hongkong	BMW	01/12/2022	0		BMW	01/12/2022	0	Mercy	01/12/2022	0
13	Malay	Mercy	01/03/2022	5
14	Malay	Mercy	01/04/2022	5
15	Malay	Mercy	01/05/2022	5
16	Malay	Mercy	01/06/2022	20
17	Malay	Mercy	01/07/2022	20
18	Malay	Mercy	01/08/2022	20
19	Malay	Mercy	01/09/2022	20
20	Malay	Mercy	01/10/2022	0
21	Malay	Mercy	01/11/2022	0
22	Malay	Mercy	01/12/2022	0
Sheet1

i want to transpose based criterai name in col. model (BMW & Mercy)
thank anyone help me out.

susant

 

[lrobbo314]

How about this?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.Split(Table.SelectColumns(Source,List.Skip(Table.ColumnNames(Source),1)),10),
    ToTables = List.Transform(List.Transform(Split, each Table.ToColumns(_)), each Table.FromRows(_)),
    lstToTable = Table.FromList(ToTables, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandTableColumn(lstToTable, "Column1", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {"Column1.1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),
    Transpose = Table.Transpose(Expand)
in
    Transpose

 

[muhammad susanto]

hi Irobbo314, working well...
thank you so much!!

 

[lrobbo314]

Found a better way.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Combine = List.Transform(
                Table.Split(
                    Table.SelectColumns(
                        Source,
                        List.Skip(Table.ColumnNames(Source),1)
                    ),
                    let 
                        lst = Source[Model],
                        cnt = List.Count(lst)/List.Count(List.Distinct(lst))
                    in
                        cnt
                ), 
                each Table.ToColumns(_)),
    Expand = Table.FromColumns(List.Accumulate(Combine,{}, (s,c)=> s & c))
in
    Expand