Combining 2 table for PL statement and Raw Data | Power Query

[lexusap]

Hi everyone,

I have an issue where I have a two tables and need to be combined together, first one is the description of Profit and Loss and second table is the raw data . The complication that I have is where In the Profit and loss has a subtotal. In the first table(GL Description) you will see there is a subtotal for A, which is summing A1 + A2 + A3 and Subtotal B (B1+ B2) and Subtotal C (C1 + C2 + C3) and Grand Total (A + B + C)

Is this possible to use both power query and power pivot to generate the result shown in row 18 onwards ?

Much appreciated for your help.

Thanks

Lex

Also crossposting here https://www.myonlinetraininghub.com/...atement#p25381

[oeldere]

D3=IFERROR(VLOOKUP(C3,RawData,2,0),0)

E3=IF(LEFT(B3,3)="sub","",LEFT(C3,1))

After that a pivot table.

See the attached file.

[alansidman]

Added a grouping to allow this data to be pivoted.

[lexusap]

Hi Alan,

Thanks Alan for your reply. I've got couple a question, first one, how do you created MergedData table (I cant find the source data?)

I am just wandering because the way that our data set up that I have another relational data table (see attached Jpg) where it tells us the GL item and the relationship
e.g Say GL A1 should return A1, A2 return A2 and GL A should return summation of A1 A2 and A3.

Can that be done in Power Query ?

The reason being that this is a sample worksheet and when it does get scale up , I do not want to hold too many data , just the lowest denominator GL and using Relationship to add them all together.

Thanks in advanced

Lex

[alansidman]

The merge table was a left inner join of your two tables. Click on Query and Connections on the Data Tab to open that window so you can look at each of the Mcodes to set that merge up.

[lexusap]

Hi,

after a weekend and refresh mind, I have added another table called relationship, this will tell that certain subtotal is addition of certain GL, can that be done.
Now I have created a table with single GL shown, but not the subtotal! Is this the way to go ? like can power query do this or is it something i need to use measure ?

Not 100% , newbie at power query here !

see v2 of P&L

Thanks
Lex

[JeteMc]

This proposal does not involve Power Query, however I feel that the output is close to what you want.
1. Add a column to the data in columns A:C that indexes the amount from column H: =IFERROR(INDEX(H$2:H$7,MATCH(C2,G$2:G$7,0)),0)
2. Add another column to identify the grouping of the GL's: =LEFT(C2,1)
3. Remove the Subtotal and Grand Total rows
4. Produce a pivot table as modeled in M17:O29
Let us know if you have any questions.