Forum Discussion

aob1's avatar
aob1
Frequent Visitor
10 years ago

Extracting date from poor source data format

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

Need Help

3 Replies

  • mike_honey's avatar
    mike_honey
    Memorable Member
    10 years ago

    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.

  • MattAllington's avatar
    MattAllington
    Community Champion
    10 years ago

    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's avatar
      aob1
      Frequent Visitor
      10 years ago

      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