Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
hlombard
Frequent Visitor

Help parse Accpac journal entries with multiple rows

I have to get hundreds of csv files, each containing thousands of journal entries, in Accpac format, into a table.

 

In Accpac format, each journal has one Header row (G) and then multiple entries below (each of which consist of a Detail row (D) and an Analytical row (A)).

Each csv file contains many journals below each other.

 

Here is a sample showing two journals (and only the first 5 of many columns).:

 

Column1Column2Column3Column4Column5
GGENAJPR2-3878nullnull
D113424.57USD
A1 null 
D2-13287.72USD
A1 null 
GGENAJRR2-4006nullnull
D1-191.52USD
A1 null 
D2191.52USD
A1 null 
D300USD
A1 null 
D4-110809.62USD
A1 null 
D5110697.28USD
A1 null 
D61112.34USD
A1 null 

 

 I would like the final result to:

 

1. Have the Analytical line next to its corresponding Detail line

2. Repeat the Header info next to its corresponding Detail and Analytical lines.

 

Like this:

 

Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Column13Column14Column15
GGENAJPR2-3878nullnullD11 3,424.57 USDA1 null 
GGENAJPR2-3879nullnullD2-1 3,287.72 USDA1 null 
GGENAJRR2-4006nullnullD1-1 91.52 USDA1 null 
GGENAJRR2-4007nullnullD21 91.52 USDA1 null 
GGENAJRR2-4008nullnullD30 -   USDA1 null 
GGENAJRR2-4009nullnullD4-1 10,809.62 USDA1 null 
GGENAJRR2-4010nullnullD51 10,697.28 USDA1 null 
GGENAJRR2-4011nullnullD61 112.34 USDA1 null 

 

 

I know how to get the csv files in and the data above is dummy data.

 

I appreciate your help.

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@hlombard,

You would need to filter rows, merge queries, create approrpriate columns to get expected result, for more details, please review this PBIX file.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@hlombard,

You would need to filter rows, merge queries, create approrpriate columns to get expected result, for more details, please review this PBIX file.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Perfect!

 

Thank you!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Top Solution Authors