Power query to create Power BI table

[claper57]

https://www.dropbox.com/scl/fi/5im1lyjgzg2pa0bhhlcil/PBIPQ.xlsx?dl=0&rlkey=fnu7pyv3na3b7oa5nmo35seq9 

Sheet 1 contains source data set example (and also fetched into Power BI as a table

Sheet 2 show how I would like to have an additional table - filtered by some of the fields in the source table

I would appreciate if someone could provide me with a pbix file describing how this task could be accomplished

[claper57]

Hello, thanks for helping. I will attach an excel source file, and a Power BI file that will show my problem. In the pbix i'll assume that source has to be altered according to where one stores theexcel file.
Thanks in advance

https://www.dropbox.com/s/opru44hkhlj9h2r/Test.pbix?dl=0 

https://www.dropbox.com/scl/fi/97o2o60bfygmgb8dtdzyh/DB.xlsx?dl=0&rlkey=nnp07r7fai5ojka80f2e8gnuq 

[edhans · Accepted solution]

See the attached file. It returns this:

Your data did not match your expected output. For example, C2 & C3 had no income values, and C1 had no expenses.

excel file here.

I would do the totals in DAX using the totals feature of a Matrix or Table.

[claper57]

Thanks a lot - this will make it possible for me to get my data into a matrix, as you suggest 🙂

[edhans]

Great @claper57 - glad I was able to help. Unpivoting and re-pivoting can be awesome transformation tools in Power Query. Have fun with the rest of your project!

[claper57]

May I ask another question? I think I would like to have this pivoted table created based on an existing Power BI table (I have already imported the one you used as source in excel) Lets say my existing table is named fProjects, and have the same columns as earlier mentioned (one record per project)
I would like to have a new table based on fProject and call it fProjectPiv. fProjectPiv will have to look like your result table, but the values has to reflect fProject values, sliced via Department etc
Is this possible?

[edhans]

I'm not sure what exactly you are asking, but you can have one table in Power Query use another as the source and then have them both load into Power BI. I'd need more details to understand the question though.