Forum Discussion

KuntalSingh's avatar
KuntalSingh
Helper V
2 years ago
Solved

Transpose data and count if condition

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 

ScriptsSourceVendorVendorNameCompanyCodeFiscalYearDocumentTypeDocumentDatePostingKeyPostingDateDocumentNumberReferenceAmountindoccurrDocumentcurrencyAmountinlocalcurrencyLocalCurrencyClearingdateTextNetduedateValueCC
S(0-25)-1SAP2003178780 US292024RE28-02-20233101-08-20245101197126199065-10.7USD-10.7USD VR:Pay-as-billed4/29/2023Below 10K51011971262024US29
S(0-25)-1SAP2003215158 US292023RE28-02-20233125-03-20235100017080199065-10.7USD-10.7USD VR:Pay-as-billed4/29/2023Below 10K51000170802023US29
S(25-60)-3SAP2000022228 US282024RE21-05-20242123-07-20245101567516912596409915.28USD15.28USD VR:Pay-as-billed9/18/2024Below 10K51015675162024US28
S(25-60)-4SAP2004365991VESTIS GROUP INCUS292024RE17-07-20243101-08-202451011490404130245299-20.47USD-20.47USD VR:Full Accrual9/15/2024Below 10K51011490402024US29
S(25-60)-2SAP110076660SUPPLYFORCE10002024RE03-12-20203101-08-20245135751891S111229788001-149.48USD-149.48USD TICKET SCTASK75073622/1/2021Below 10K513575189120241000
S(60-87)-2SAP110077236KIRBY RISK ELECTRICAL SUPPLY10002024ZC03-12-20202101-08-20245135751741S111229788001149.48USD149.48USD MR8M2/1/2021Below 10K513575174120241000
S(60-87)-2SAP110013648INGERSOLL RAND CO10002023RE11-07-20233117-07-2023513247631031071227-311.61USD-311.61USD  9/9/2023Below 10K513247631020231000
S(60-87)-5SAP10037358GRAINGER INC10002024RE23-10-20232131-07-20245135738340988002180482.24USD82.24USD7/31/2024SCTASK754128510/23/2023Below 10K513573834020241000
S(87-100)-1SAP2000019838 US292024RE30-07-20242101-08-20245101988001204086363319.62USD63319.62USD VR:Partial_Accrual9/28/2024Above 10K51019880012024US29
S(87-100)-1SAP110086387TOMAHAWK INFORMATION SOLUTIONS10002023RE04-05-20233106-12-202351337796090000604IN-78680.25USD-78680.25USD  7/3/2023Above 10K513377960920231000
S(87-100)-2SAP110056591QUALYSENSE AG10002024RE31-01-20242131-07-20245135738461202379172DMA136500USD18886.14USD8/1/202451331968951/31/2024Above 10K513573846120241000
S(87-100)-2SAP2003175551 US302024RE02-02-20243114-05-2024510143600130089232146-8871.45USD-8871.45USD VR:Pay-as-billed3/8/2024Below 10K51014360012024US30

 

Output file lay out shuold be 

CCS(0-25)-1S(25-60)-1S(25-60)-2S(25-60)-3S(25-60)-4S(60-87)-1S(60-87)-2S(60-87)-3S(60-87)-4S(60-87)-5S(87-100)-1S(87-100)-2S(87-100)-3S(87-100)-4S(87-100)-5Column1
51011971262024US291000000000000001
51000170802023US291000000000000001
51014135282024US281000000000000001
51014915912024US281000000000000001
51011490402024US291000000000000001
51014135272024US281000000000000001
5135751741202410000100000000000001
5132476310202310000111100000000004

 

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.

 

 

 

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    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

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    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

  • p45cal's avatar
    p45cal
    Solution Supplier
    2 years ago

    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