Transpose data and count if condition

[KuntalSingh]

Rules are 

Concatenate Document Number, Year and Company Code in Input sheet - Copy and Paste in New sheet.
Remove Duplicates
Copy Scripts from A Columns(Input sheet) and paste it a in New sheet
Remove Duplicates the Scripts.
Copy the scripts and paste it as transpose.
Apply CountIF formula Based on Scripts
if counts matches in all the scripts then we can keep only the first one and flag other scripts in the Input file.

Input data 

Scripts	Source	Vendor	VendorName	CompanyCode	FiscalYear	DocumentType	DocumentDate	PostingKey	PostingDate	DocumentNumber	Reference	Amountindoccurr	Documentcurrency	Amountinlocalcurrency	LocalCurrency	Clearingdate	Text	Netduedate	Value	CC
S(0-25)-1	SAP	2003178780		US29	2024	RE	28-02-2023	31	01-08-2024	5101197126	199065	-10.7	USD	-10.7	USD		VR:Pay-as-billed	4/29/2023	Below 10K	51011971262024US29
S(0-25)-1	SAP	2003215158		US29	2023	RE	28-02-2023	31	25-03-2023	5100017080	199065	-10.7	USD	-10.7	USD		VR:Pay-as-billed	4/29/2023	Below 10K	51000170802023US29
S(25-60)-3	SAP	2000022228		US28	2024	RE	21-05-2024	21	23-07-2024	5101567516	9125964099	15.28	USD	15.28	USD		VR:Pay-as-billed	9/18/2024	Below 10K	51015675162024US28
S(25-60)-4	SAP	2004365991	VESTIS GROUP INC	US29	2024	RE	17-07-2024	31	01-08-2024	5101149040	4130245299	-20.47	USD	-20.47	USD		VR:Full Accrual	9/15/2024	Below 10K	51011490402024US29
S(25-60)-2	SAP	110076660	SUPPLYFORCE	1000	2024	RE	03-12-2020	31	01-08-2024	5135751891	S111229788001	-149.48	USD	-149.48	USD		TICKET SCTASK7507362	2/1/2021	Below 10K	513575189120241000
S(60-87)-2	SAP	110077236	KIRBY RISK ELECTRICAL SUPPLY	1000	2024	ZC	03-12-2020	21	01-08-2024	5135751741	S111229788001	149.48	USD	149.48	USD		MR8M	2/1/2021	Below 10K	513575174120241000
S(60-87)-2	SAP	110013648	INGERSOLL RAND CO	1000	2023	RE	11-07-2023	31	17-07-2023	5132476310	31071227	-311.61	USD	-311.61	USD			9/9/2023	Below 10K	513247631020231000
S(60-87)-5	SAP	10037358	GRAINGER INC	1000	2024	RE	23-10-2023	21	31-07-2024	5135738340	9880021804	82.24	USD	82.24	USD	7/31/2024	SCTASK7541285	10/23/2023	Below 10K	513573834020241000
S(87-100)-1	SAP	2000019838		US29	2024	RE	30-07-2024	21	01-08-2024	5101988001	2040863	63319.62	USD	63319.62	USD		VR:Partial_Accrual	9/28/2024	Above 10K	51019880012024US29
S(87-100)-1	SAP	110086387	TOMAHAWK INFORMATION SOLUTIONS	1000	2023	RE	04-05-2023	31	06-12-2023	5133779609	0000604IN	-78680.25	USD	-78680.25	USD			7/3/2023	Above 10K	513377960920231000
S(87-100)-2	SAP	110056591	QUALYSENSE AG	1000	2024	RE	31-01-2024	21	31-07-2024	5135738461	202379172DMA	136500	USD	18886.14	USD	8/1/2024	5133196895	1/31/2024	Above 10K	513573846120241000
S(87-100)-2	SAP	2003175551		US30	2024	RE	02-02-2024	31	14-05-2024	5101436001	30089232146	-8871.45	USD	-8871.45	USD		VR:Pay-as-billed	3/8/2024	Below 10K	51014360012024US30

Output file lay out shuold be 

CC	S(0-25)-1	S(25-60)-1	S(25-60)-2	S(25-60)-3	S(25-60)-4	S(60-87)-1	S(60-87)-2	S(60-87)-3	S(60-87)-4	S(60-87)-5	S(87-100)-1	S(87-100)-2	S(87-100)-3	S(87-100)-4	S(87-100)-5	Column1
51011971262024US29	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
51000170802023US29	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
51014135282024US28	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
51014915912024US28	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
51011490402024US29	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
51014135272024US28	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
513575174120241000	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1
513247631020231000	0	1	1	1	1	0	0	0	0	0	0	0	0	0	0	4

My Code 

let
Source = Excel.Workbook(File.Contents("C:\Users\KUNTALSINGH\Box\PepsiCo - NA Process Session\ACL Output file.xlsx"), null, true),
Inputsheet_Sheet = Source{[Item="Inputsheet",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Inputsheet_Sheet, [PromoteAllScalars=true]),
AddConcatenatedColumn = Table.AddColumn(#"Promoted Headers", "CC", each Text.From([DocumentNumber]) & Text.From([FiscalYear]) & [CompanyCode]),
RemoveOtherColumns = Table.SelectColumns(AddConcatenatedColumn,{"CC", "Scripts"}),
RemoveDuplicates = Table.Distinct(RemoveOtherColumns),
GroupByCC = Table.Group(#"RemoveDuplicates", {"CC"}, {{"ScriptsList", each _, type table [CC=text, Scripts=text]}}),
AddScriptColumn = Table.AddColumn(GroupByCC, "Scripts", each List.Distinct([ScriptsList][Scripts])),
TransposeScripts = Table.Transpose(Table.SelectColumns(AddScriptColumn, {"Scripts"})),
ScriptHeaders = Table.FirstN(TransposeScripts,1),
FlagMatrix = Table.AddColumn(Table.RemoveFirstN(TransposeScripts,1), "FlagMatrix", each [Column1] & [Column2]),
PromoteHeaders = Table.PromoteHeaders(FlagMatrix, [PromoteAllScalars=true])
in
PromoteHeaders.

It shows table as empty . Can you please check and help.

[p45cal]

I'm wondering whether you want this:

In the linked-to workbook below, the output (Pivot table) of a Power Query query at cell V2.

There's no need to concatenate fields before grouping since you can group on more than one field.

There are no duplicate Scripts for the same Document Number, Year and Company Code combination in your sample data; if there were you'd see that reflected in the pivot table.

The workbook: https://app.box.com/s/7cej69qpsh2bwn6lo6k6hj17quzrem88

[bhanu_gautam · Accepted solution]

@KuntalSingh , Try using below m code

let
// Load the Excel file and the Input sheet
Source = Excel.Workbook(File.Contents("C:\Users\KUNTALSINGH\Box\PepsiCo - NA Process Session\ACL Output file.xlsx"), null, true),
Inputsheet_Sheet = Source{[Item="Inputsheet",Kind="Sheet"]}[Data],

// Promote headers
#"Promoted Headers" = Table.PromoteHeaders(Inputsheet_Sheet, [PromoteAllScalars=true]),

// Add concatenated column
AddConcatenatedColumn = Table.AddColumn(#"Promoted Headers", "CC", each Text.From([DocumentNumber]) & Text.From([FiscalYear]) & [CompanyCode]),

// Remove duplicates
RemoveDuplicates = Table.Distinct(AddConcatenatedColumn),

// Select only the necessary columns
SelectColumns = Table.SelectColumns(RemoveDuplicates, {"CC", "Scripts"}),

// Group by CC and create a list of Scripts
GroupByCC = Table.Group(SelectColumns, {"CC"}, {{"ScriptsList", each _, type table [CC=text, Scripts=text]}}),

// Add a column with distinct scripts
AddScriptColumn = Table.AddColumn(GroupByCC, "Scripts", each List.Distinct([ScriptsList][Scripts])),

// Expand the Scripts column
ExpandScripts = Table.ExpandListColumn(AddScriptColumn, "Scripts"),

// Pivot the table to transpose the scripts
PivotScripts = Table.Pivot(ExpandScripts, List.Distinct(ExpandScripts[Scripts]), "Scripts", "CC", List.Count),

// Replace nulls with 0
ReplaceNulls = Table.ReplaceValue(PivotScripts, null, 0, Replacer.ReplaceValue, List.Distinct(ExpandScripts[Scripts])),

// Add a column to count the flags
AddFlagColumn = Table.AddColumn(ReplaceNulls, "FlagMatrix", each List.Sum(Record.ToList(Record.RemoveFields(_, {"CC"})))),

// Promote headers
PromoteHeaders = Table.PromoteHeaders(AddFlagColumn, [PromoteAllScalars=true])
in
PromoteHeaders