Transpose data and count if condition

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