Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aob1
Frequent Visitor

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.

PowerBI_Issue.pngAt 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

3 REPLIES 3

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?



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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
Memorable Member
Memorable Member

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.