topic Re: Need help on M code in Power Query

Need help on M code

KuntalSingh — Tue, 13 Aug 2024 07:27:44 GMT

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

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	19906510.7
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	19906510.7
S(0-25)-1	SAP	2003155708		US28	2024	RE	11-07-2024	21	31-07-2024	5101413528	NY15-00064666	12.9	USD	12.9	USD		MR8M	11/8/2024	Below 10K	51014135282024US28	NY15-0006466612.9
S(0-25)-1	SAP	2003155708		US28	2024	RE	11-07-2024	31	20-07-2024	5101491591	NY15-00064666	-12.9	USD	-12.9	USD			11/8/2024	Below 10K	51014915912024US28	NY15-0006466612.9
S(0-25)-1	SAP	2000022228		US28	2024	RE	21-05-2024	31	23-07-2024	5101566015	9125964099	-15.28	USD	-15.28	USD		VR:Pay-as-billed	9/18/2024	Below 10K	51015660152024US28	912596409915.28
S(0-25)-1	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	912596409915.28
S(0-25)-1	SAP	2000022228		US28	2024	RE	21-05-2024	21	31-07-2024	5101752521	9125964099	15.28	USD	15.28	USD		VR:Pay-as-billed	9/18/2024	Below 10K	51017525212024US28	912596409915.28
S(0-25)-1	SAP	2000022228		US28	2024	RE	21-05-2024	31	05-06-2024	5101874011	9125964099	-15.28	USD	-15.28	USD		VR:Pay-as-billed	9/18/2024	Below 10K	51018740112024US28	912596409915.28
S(0-25)-1	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	413024529920.47
S(0-25)-1	SAP	2004365991	VESTIS GROUP INC	US29	2024	RE	17-07-2024	21	01-08-2024	5101984655	4130245299	20.47	USD	20.47	USD		VR:Full Accrual	9/15/2024	Below 10K	51019846552024US29	413024529920.47
S(0-25)-1	SAP	2003155708		US28	2024	RE	05-07-2024	21	31-07-2024	5101413527	NY15-00064588	20.5	USD	20.5	USD		VR:Full Accrual	11/2/2024	Below 10K	51014135272024US28	NY15-0006458820.5
S(0-25)-1	SAP	2003155708		US28	2024	RE	05-07-2024	31	11-07-2024	5101575516	NY15-00064588	-20.5	USD	-20.5	USD		VR:Full Accrual	11/2/2024	Below 10K	51015755162024US28	NY15-0006458820.5
S(25-60)-1	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	S111229788001149.48
S(25-60)-1	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	S111229788001149.48
S(25-60)-1	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	31071227311.61
S(25-60)-1	SAP	110013648	INGERSOLL RAND CO	1000	2024	RE	11-07-2023	31	31-07-2024	5135737969	31071227	-311.61	USD	-311.61	USD		TICKET SCTASK7538918	9/9/2023	Below 10K	513573796920241000	31071227311.61
S(25-60)-1	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	988002180482.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

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
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
51015660152024US28	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
51015675162024US28	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
51017525212024US28	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
51018740112024US28	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
51019846552024US29	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
51015755162024US28	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
513575189120241000	0	1	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
513573796920241000	0	1	1	1	1	0	0	0	0	0	0	0	0	0	0	4
513573834020241000	0	1	1	1	1	0	0	0	0	0	0	0	0	0	0	4

Re: Need help on M code

KNP — Tue, 13 Aug 2024 18:18:30 GMT

And what part are you stuck on?

Re: Need help on M code

KuntalSingh — Wed, 14 Aug 2024 05:19:29 GMT

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

Re: Need help on M code

KNP — Wed, 14 Aug 2024 19:24:22 GMT

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-d5d42d9f0a8d

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

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

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.

Re: Need help on M code

ahadkarimi — Thu, 15 Aug 2024 03:40:09 GMT

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

Re: Need help on M code

KuntalSingh — Fri, 16 Aug 2024 04:48:04 GMT

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

Kindly check and help

Re: Need help on M code

KuntalSingh — Tue, 20 Aug 2024 08:14:01 GMT

Please help

Re: Need help on M code

KuntalSingh — Thu, 22 Aug 2024 06:41:37 GMT

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.