Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a live query to an SQL server returning some finance fields and the source data is as per table below.
I have only used the Jan, Feb, Mar field as an example but I have a full year dataset. There is no primary key, but the individual record distinction is based on "JobNo, Year"
What I am trying to do is plot the mopnthly invoices and costs for a given year but I am limited on doing this in PowerBI due to the poor source table setup. Ideally, I'd have a seperate table for billings, seperate table for costs etc. I don't have access to change the source layout in SQL.
At a stretch, I figure I could turn off live query and do some external manipulation of the tables in Access or a seperate SQL instance and import that, but thought I'd see if anyone else had ideas first
Thanks
Do what Mike says, then
You can unpivot the data and convert it to your needs
select the first 2 columns and then select "unpivot other columns"
Split the attribute column in the "_" character
replace b with billing
replace c with cost
rename columns
this gives a data table that is more usable. Does this make sense?
Thanks guys. I get the concept, I'll have a play with the unpivot feature and see what I can come up with. Much appreciated
I would turn off direct query and import the data, but I would transform it in the PBI Desktop Edit Queries window. The Unpivot Columns transformation will twist those columns into rows, to get a useable result. Queries can also automate splitting the column headers and converting the Months into dates.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |