Extracting date from poor source data format

[aob1]

Hi all,

I have a live query to an SQL server returning some finance fields and the source data is as per table below.

• B_Jan, B_Feb, B_Mar are the amounts invoiced to a customer in a given month.
• C_Jan, C_Feb, C_Mar are the costs incurred on a job in a given month.

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

[MattAllington]

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?

[aob1]

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 

[mike_honey]

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.