The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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).:
Column1 | Column2 | Column3 | Column4 | Column5 |
G | GENAJ | PR2-3878 | null | null |
D | 1 | 1 | 3424.57 | USD |
A | 1 | null | ||
D | 2 | -1 | 3287.72 | USD |
A | 1 | null | ||
G | GENAJ | RR2-4006 | null | null |
D | 1 | -1 | 91.52 | USD |
A | 1 | null | ||
D | 2 | 1 | 91.52 | USD |
A | 1 | null | ||
D | 3 | 0 | 0 | USD |
A | 1 | null | ||
D | 4 | -1 | 10809.62 | USD |
A | 1 | null | ||
D | 5 | 1 | 10697.28 | USD |
A | 1 | null | ||
D | 6 | 1 | 112.34 | USD |
A | 1 | 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:
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 | Column10 | Column11 | Column12 | Column13 | Column14 | Column15 |
G | GENAJ | PR2-3878 | null | null | D | 1 | 1 | 3,424.57 | USD | A | 1 | null | ||
G | GENAJ | PR2-3879 | null | null | D | 2 | -1 | 3,287.72 | USD | A | 1 | null | ||
G | GENAJ | RR2-4006 | null | null | D | 1 | -1 | 91.52 | USD | A | 1 | null | ||
G | GENAJ | RR2-4007 | null | null | D | 2 | 1 | 91.52 | USD | A | 1 | null | ||
G | GENAJ | RR2-4008 | null | null | D | 3 | 0 | - | USD | A | 1 | null | ||
G | GENAJ | RR2-4009 | null | null | D | 4 | -1 | 10,809.62 | USD | A | 1 | null | ||
G | GENAJ | RR2-4010 | null | null | D | 5 | 1 | 10,697.28 | USD | A | 1 | null | ||
G | GENAJ | RR2-4011 | null | null | D | 6 | 1 | 112.34 | USD | A | 1 | null |
I know how to get the csv files in and the data above is dummy data.
I appreciate your help.
Solved! Go to Solution.
@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
@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
Perfect!
Thank you!