Transformation before Exporting data to SAP system

MA 101

Hi: I would greatly appreciate some help in relation to converting an excel file into an uploadable format to export data to an ERP system. I am not sure if this is possible in PQ.

1) In the input file, when there is a new customer # starts then three lines needs to be inserted highlighted in yellow (output file). These three lines start with AR. In "AR ITM" line needs to be shown as DR K3 cell and the amount in J3 cell is the sum of that customer if there are multiple lines.

2) The date in the E1 and F1 cells would be todays date. Any other information in the "AR" line items do not exist in the Input file then they are hard coded.

3) After inserting three lines as AR then each line in the input file need to shown as two lines starting as "GL". Second line in each pair "GL ITL" e.g cell E5 represents output as a result of concatenating of multiple cells in input file.

4) The pattern in column A , B & C are shown in the output file.

Please let me know if you need further clarifications. Thank you in advance.

Accepted answer

Lz._ 8,991 Reputation points

2021-01-19T21:28:43.643+00:00

Hi @MA

No idea how you wanted/needed the output column to be named, opted for Field1, Field2... - easy to change in fxSummary and fxDetails

Query available for download here. Looks good to me according to the picture you posted but double-check and confirm please
Please sign in to rate this answer.
MA 101 Reputation points

2021-01-19T23:46:54.53+00:00

Hi Lz: Long time no see. Hope you are doing fine. I will go through the solution and come back to you. Sorry, forgot to mention in the output file column/Header are not required. Thank you very much.

MA 101 Reputation points

2021-01-20T00:27:12.717+00:00

I cant get head around how the "FieldValues{1}, {2} etc are picking up the values? I can see that its being defined as function. There is no parameter

I have added the following two steps to remove the header in the final query.
DH = Table.DemoteHeaders(OutTable),
RT = Table.Skip(DH,1)

Thanks Lz

MA 101 Reputation points

2021-01-20T13:22:03.293+00:00

Lz Thanks for the solution, clever coding. Greatly appreciated.

Lz._ 8,991 Reputation points

2021-01-21T02:15:38.133+00:00

Hi @MA

(Don't see who you are :( What was your pseudo on the former forum?)

Re. FieldValues{0}, FieldValues{1}... Sample updated. See comments in query Output step GroupedRows
then look at fxDetails where I changed the name of the list argument/parameter

EDIT: this version passes a Record (instead of a List) to each function. With the record field names this is clearer and easier to follow

Hope this clarifies things otherwise let me know

MA 101 Reputation points

2021-01-22T05:07:30.25+00:00

Hi Lz: Thanks for your reply. I can see inside the Table.Group func you created a list. In the FxDetails you have a list parameter called foo. Inside this fuc which column is being selected?

I need to make couple of more changes with the output. I have the outlined the changes in the file. Would you be able to assist further on this please?
Thanks

https://1drv.ms/x/s!Amc8fiGpDxeki2BczUEgUMuyVU0v?e=6I5PO7
Sign in to comment

1 additional answer

Lz._ 8,991 Reputation points

2021-01-22T09:14:38.307+00:00

Hi @MA (M.Awal ???)

Due to your requirement for [Field3] (= Customer index) I've taken a different approach
Revised version available here
Please sign in to rate this answer.
MA 101 Reputation points

2021-01-22T13:12:34.607+00:00

Hi Lz: You recognized me correctly. I didn't realise that the user names didn't get transition to this new site.

Thanks for incorporating additional changes in the solution. It looks exactly what I wanted.

Have a good one.

Lz._ 8,991 Reputation points

2021-01-22T14:09:21.887+00:00

Hey Awal

In the meantime I made a little change to make things clearer/easier to follow => available here

MA 101 Reputation points

2021-01-23T22:39:16.247+00:00

Thanks again Lz. Starting to make sense as I am going through each step.
I may come back to you later if I get stuck in understand in any of the steps.

Cheers
Sign in to comment