Merging two CSV on job profitability where like jobs need to be added together...

El Cid

El Cid

Member
Joined
Aug 22, 2016
Messages
52
Reaction score
0
Points
6
Location
Greenville, SC
Excel Version(s)
Excel 2016
I'm trying to merge two CSV files of similar data from different time periods. I had to break them up by time period, example, Jan-Mar and Apr-Jun because the output software limits the output to 256 columns. I normally only have to merge 1 of these files with a "master file" that contains all of my GL account numbers and I'm having no problem with that...but this wrench was thrown into the mess and I'm stumped:

Example Master CVS File:

41105 Contract Labor
41110 Contract Materials
41111 Contract Equipment Rental

The detail data contains by job number across the top the with the GL numbers along the left side like:

CSV File 1:
45678 45894
41105 Contract Labor 1500.00 1400.00
41111 Contract Equipment Rental 4500.00 5500.00

There is the possibility that the detail CSV files will have the same job number listed twice. These are a duplicate, but not a duplicate. Their totals need to be added together and duplicate eliminated.

CSV File 2:
45678
41105 Contract Labor 1500.00
41111 Contract Equipment Rental 4500.00

Final merge should look like this:

CSV File Merge:
45678 45894
41105 Contract Labor 3000.00 1400.00
41110 Contract Materials
41111 Contract Equipment Rental 9000.00 5500.00

After the merge and add I plan to transpose the final for output.

Anyone have a suggestion to slay this monster? Thanks

I'm thinking that I may have to do both separately, dump to Excel and use pivot tables to marry them (do the addition), but would prefer to do it all in PQ.
 
This can be done in PQ.

This will process all files in a folder.

Code: 
let
    Source = Folder.Files("C:\\ExcelGuru 7115"),
    #"Lowercased Text" = Table.TransformColumns(Source,{{"Extension", Text.Lower}}),
    #"Filtered Rows" = Table.SelectRows(#"Lowercased Text", each [Extension] = ".csv"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Data", each Table.UnpivotOtherColumns(Table.PromoteHeaders(Csv.Document([Content])), {"GL/Job Number"}, "Attribute", "Value")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"Atribute", "GL/Job Number", "Value"}, {"Attribute", "GL/Job Number", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"Attribute", Int64.Type}, {"GL/Job Number", Int64.Type}, {"Value", type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Attribute", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Attribute", type text}}, "en-GB")[Attribute]), "Atribute", "Value", List.Sum)
in
    #"Pivoted Column"

However, this will not work straight away. Your example data didn't appear to have a header for column 1 so I have called it "GL/Job Number" in the above.
 
Comfy said:
However, this will not work straight away. Your example data didn't appear to have a header for column 1 so I have called it "GL/Job Number" in the above.
Click to expand...

Thanks for your help. My bad...the forum changed my formatting. I've uploaded the files that are close to what I'm working with but had to go ahead and convert to xlsx because I couldn't get the forum to find the csv files.

Here's my scripts:
//This function pulls in the path of the the current workbook named fnGetParameter - I have to do this because the path changes every month
(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter]=ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value

//Next I connect load the YTDP&L and clean it up. This is the left side in my merge. Property Name: Standard P&L
let
SolutionPath = fnGetParameter("File Path"),
Source = Csv.Document(File.Contents(SolutionPath&"YTDP&L.CSV"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Transposed Table" = Table.Transpose(Source),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table"),
//Next remove all GL Accounts that come after Gross Profit
#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Expense", "Employee Drug Testing", "60120 · Salaries & Wages - G&A", "60200 · Employer IRA Expense", "60300 · Payroll Taxes", "60301 · FICA Expense", "60302 · FUTA Expense", "60303 · SUTA Expense", "60300 · Payroll Taxes - Other", "Total 60300 · Payroll Taxes"}),
#"Demoted Headers" = Table.DemoteHeaders(#"Removed Columns"),
#"Transposed Table1" = Table.Transpose(#"Demoted Headers"),
#"Renamed Columns" = Table.RenameColumns(#"Transposed Table1",{{"Column1", "GLACCT"}, {"Column2", "AMOUNT"}})
in
#"Renamed Columns"

//Next I load the connect load the first YTDSERVJOBSP&L01.csv file...Property Name: Service Jobs P&L
let
SolutionPath = fnGetParameter("File Path"),
Source = Csv.Document(File.Contents(SolutionPath & "YTDSERVJOBSP&L01.CSV"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Transposed Table" = Table.Transpose(Source),
#"Filtered Rows" = Table.SelectRows(#"Transposed Table", each not Text.Contains([Column2], "Total")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column2", "Column3", "Column4", "Column5", "Column6"}),
#"Transposed Table1" = Table.Transpose(#"Removed Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1"),
#"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers",{{"", "GLACCT"}, {"_1", "ServTTL"}})
in
#"Renamed Columns1"
At this point I have to figure out a way to load the second data file, YTDSERVJOBSP&L02.CSV and have all three merge finally merge. All of the numbers under the duplicate headers (job numbers) in YTDSERVJOBSP&L01.CSV and YTDSERVJOBSP&L02.CSV must be added together because they are from two separate periods. You will notice in the attached that some jobs numbers are only listed in one file but some are listed twice. The last column in both, renamed "ServTTL" should be added together also.
 
And what would the final output be??

Account Code - Account Description | YTD Amount from P&L File | Service JOBS from Other files --->>>>
 
Hopefully this will be enough to point you in the right direction??

I converted your Query for loading YTDSERVJOBSP&L01.csv into a Function and adjusted it slightly to unpivot the data.
The tow Job csvs go into a sub folder (added as a parameter to your parameter table, 201710 in the example)
We then load all the files in this folder and use the above function to process them.
 

Attachments

  • 7115.xlsx
    35.7 KB · Views: 16
Comfy said:
Hopefully this will be enough to point you in the right direction??

I converted your Query for loading YTDSERVJOBSP&L01.csv into a Function and adjusted it slightly to unpivot the data.
The tow Job csvs go into a sub folder (added as a parameter to your parameter table, 201710 in the example)
We then load all the files in this folder and use the above function to process them.
Click to expand...

Awesome... I'm understanding it all now! LOL. Thanks for the great logic here. For some reason the fnGetParameter wouldn't fire off on my version of Excel 2016. I just bypassed it and copy-paste the correct path in the parameter field it was supposed to load into...probably some stinking security feature blocking it on my end. Then fnGetMonthlyData choked on "_1" and threw a wrench into the entire works so I fixed that bugger. Once I fixed those things everything ran like clockwork.
 
You must log in or register to reply here.
Back
Top