Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
KuntalSingh
Helper V
Helper V

Need help on M code

‎08-13-2024 12:27 AM

Hi All- Need help to convert input data into request output.

Input data

ScriptsSourceVendorVendorNameCompanyCodeFiscalYearDocumentTypeDocumentDatePostingKeyPostingDateDocumentNumberReferenceAmountindoccurrDocumentcurrencyAmountinlocalcurrencyLocalCurrencyClearingdateTextNetduedateValueCC 
S(0-25)-1SAP2003178780 US292024RE28-02-20233101-08-20245101197126199065-10.7USD-10.7USD VR:Pay-as-billed4/29/2023Below 10K51011971262024US2919906510.7
S(0-25)-1SAP2003215158 US292023RE28-02-20233125-03-20235100017080199065-10.7USD-10.7USD VR:Pay-as-billed4/29/2023Below 10K51000170802023US2919906510.7
S(0-25)-1SAP2003155708 US282024RE11-07-20242131-07-20245101413528NY15-0006466612.9USD12.9USD MR8M11/8/2024Below 10K51014135282024US28NY15-0006466612.9
S(0-25)-1SAP2003155708 US282024RE11-07-20243120-07-20245101491591NY15-00064666-12.9USD-12.9USD  11/8/2024Below 10K51014915912024US28NY15-0006466612.9
S(0-25)-1SAP2000022228 US282024RE21-05-20243123-07-202451015660159125964099-15.28USD-15.28USD VR:Pay-as-billed9/18/2024Below 10K51015660152024US28912596409915.28
S(0-25)-1SAP2000022228 US282024RE21-05-20242123-07-20245101567516912596409915.28USD15.28USD VR:Pay-as-billed9/18/2024Below 10K51015675162024US28912596409915.28
S(0-25)-1SAP2000022228 US282024RE21-05-20242131-07-20245101752521912596409915.28USD15.28USD VR:Pay-as-billed9/18/2024Below 10K51017525212024US28912596409915.28
S(0-25)-1SAP2000022228 US282024RE21-05-20243105-06-202451018740119125964099-15.28USD-15.28USD VR:Pay-as-billed9/18/2024Below 10K51018740112024US28912596409915.28
S(0-25)-1SAP2004365991VESTIS GROUP INCUS292024RE17-07-20243101-08-202451011490404130245299-20.47USD-20.47USD VR:Full Accrual9/15/2024Below 10K51011490402024US29413024529920.47
S(0-25)-1SAP2004365991VESTIS GROUP INCUS292024RE17-07-20242101-08-20245101984655413024529920.47USD20.47USD VR:Full Accrual9/15/2024Below 10K51019846552024US29413024529920.47
S(0-25)-1SAP2003155708 US282024RE05-07-20242131-07-20245101413527NY15-0006458820.5USD20.5USD VR:Full Accrual11/2/2024Below 10K51014135272024US28NY15-0006458820.5
S(0-25)-1SAP2003155708 US282024RE05-07-20243111-07-20245101575516NY15-00064588-20.5USD-20.5USD VR:Full Accrual11/2/2024Below 10K51015755162024US28NY15-0006458820.5
S(25-60)-1SAP110076660SUPPLYFORCE10002024RE03-12-20203101-08-20245135751891S111229788001-149.48USD-149.48USD TICKET SCTASK75073622/1/2021Below 10K513575189120241000S111229788001149.48
S(25-60)-1SAP110077236KIRBY RISK ELECTRICAL SUPPLY10002024ZC03-12-20202101-08-20245135751741S111229788001149.48USD149.48USD MR8M2/1/2021Below 10K513575174120241000S111229788001149.48
S(25-60)-1SAP110013648INGERSOLL RAND CO10002023RE11-07-20233117-07-2023513247631031071227-311.61USD-311.61USD  9/9/2023Below 10K51324763102023100031071227311.61
S(25-60)-1SAP110013648INGERSOLL RAND CO10002024RE11-07-20233131-07-2024513573796931071227-311.61USD-311.61USD TICKET SCTASK75389189/9/2023Below 10K51357379692024100031071227311.61
S(25-60)-1SAP10037358GRAINGER INC10002024RE23-10-20232131-07-20245135738340988002180482.24USD82.24USD7/31/2024SCTASK754128510/23/2023Below 10K513573834020241000988002180482.24

 

Formulas to applied

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.

Output file

 

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)-5
51011971262024US291000000000000001
51000170802023US291000000000000001
51014135282024US281000000000000001
51014915912024US281000000000000001
51015660152024US281000000000000001
51015675162024US281000000000000001
51017525212024US281000000000000001
51018740112024US281000000000000001
51011490402024US291000000000000001
51019846552024US291000000000000001
51014135272024US281000000000000001
51015755162024US281000000000000001
5135751891202410000100000000000001
5135751741202410000100000000000001
5132476310202310000111100000000004
5135737969202410000111100000000004
5135738340202410000111100000000004

 

KuntalSingh_0-1723533639342.png

 

Labels:
Message 1 of 8
575 Views
0
7 REPLIES 7
ahadkarimi
Solution Specialist
Solution Specialist

‎08-14-2024 08:40 PM

Hey @KuntalSingh, try the code below and let me know if you encounter any issues.

Concatenate Document Number, Fiscal Year, and Company Code:

let
    Source = Excel.CurrentWorkbook(){[Name="InputTable"]}[Content],
    AddConcatenatedColumn = Table.AddColumn(Source, "CC", each Text.From([DocumentNumber]) & Text.From([FiscalYear]) & [CompanyCode]),
    RemoveOtherColumns = Table.SelectColumns(AddConcatenatedColumn,{"CC", "Scripts"})
in
    RemoveOtherColumns

Remove Duplicates:

let
    RemoveDuplicates = Table.Distinct(RemoveOtherColumns)
in
    RemoveDuplicates

Transpose Scripts and Create Flag Matrix:

let
    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

ApplyCountIf:

let
    ApplyCountIf = Table.AddColumn(RemoveDuplicates, "Count", each List.CountIf(TransposeScripts[ScriptsList], each _ = [Scripts])),
    FlagScripts = Table.TransformColumns(ApplyCountIf, {"Count", each if _ = 1 then 1 else 0})
in
    FlagScripts

FinalOutput:

let
    FinalOutput = Table.TransformColumns(FlagScripts, {"FlagMatrix", each if List.Sum(FlagScripts[Count]) > 0 then 1 else 0})
in
    FinalOutput

 

Message 5 of 8
468 Views
0
KuntalSingh
Helper V
Helper V
In response to ahadkarimi

‎08-21-2024 11:41 PM

Hi Ahadkarimi

This is 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.

KuntalSingh_0-1724308881395.png

 

 

Message 8 of 8
347 Views
0
KuntalSingh
Helper V
Helper V
In response to ahadkarimi

‎08-15-2024 09:48 PM

Thank you very much for your kind support

I am new to Power query 

I have merge all the code in single as 

 

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(#"ScriptHeaders",1), "FlagMatrix", each [Column1] & [Column2]),
#"PromoteHeaders" = Table.PromoteHeaders(#"FlagMatrix", [PromoteAllScalars=true]),
#"ApplyCountIf" = Table.AddColumn(#"PromoteHeaders", "Count", each List.CountIf(TransposeScripts[ScriptsList], each _ = [Scripts])),
#"FlagScripts" = Table.TransformColumns(#"ApplyCountIf", {"Count", each if _ = 1 then 1 else 0}),
#"FinalOutput" = Table.TransformColumns(#"FlagScripts", {"FlagMatrix", each if List.Sum(FlagScripts[Count]) > 0 then 1 else 0})
in
#"FinalOutput"

 

Output shows empty 

KuntalSingh_0-1723783658291.png

Kindly check and help

Message 6 of 8
446 Views
0
KuntalSingh
Helper V
Helper V
In response to KuntalSingh

‎08-20-2024 01:14 AM

Please help

Message 7 of 8
374 Views
0
KNP
Super User
Super User

‎08-14-2024 12:24 PM

I started working on something for you, but honestly, you're basically asking someone to do all the work for you.

You need to ask more specific questions, provide more detail, and do a little research.

 

Concatenate: https://support.microsoft.com/en-us/office/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9...

Remove Duplicates: https://support.microsoft.com/en-us/office/keep-or-remove-duplicate-rows-power-query-d9cffc69-dc5d-4...

Count If: https://stackoverflow.com/questions/35351610/how-to-do-countifs-sumifs-in-powerquery-m-language-form...

 

I attached a PBIX file but it is only what I started working on. I don't have time to do more without more specific questions.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).		xOIEmaj

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
image		fabric-SUbadge
Proud to be a Super User!
KuntalSingh.pbix
Message 4 of 8
479 Views
0
KNP
Super User
Super User

‎08-13-2024 11:18 AM

And what part are you stuck on?

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).		xOIEmaj

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
image		fabric-SUbadge
Proud to be a Super User!
Message 2 of 8
528 Views
0
KuntalSingh
Helper V
Helper V
In response to KNP

‎08-13-2024 10:19 PM

Thanks for your prompt reply

 

Would request you to please help me from start I import the data after that I stuck how to get the desire output

Message 3 of 8
500 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All
Top Solution Authors
View All