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
Anonymous
Not applicable

Data Modeling Help

Newb here to PowerBi and DAX.

 

I have multiple files in very similar formats for different fiscal years:

 

YearOrgIDValue1Value2
20152016001001226
20152016001002586378

 

YearOrgIDValue1Value2
20142015001001325
20142015001003596399

 

The data comes via Excel, and I have four years of these files. Each file is formatted similarly (but not necessarily exactly), and the Year field is formatted as text like above. There is no guarantee that an Organization will be there from one year to the next. Plus, I have a corresponding Profile data file for each organization for each year. I want to allow the user to slice the data by year and organization, and probably other fields as well.

 

Should I flatten all the data into one table, and all the profiles into its own table, or one big table, or keep them all separate? If separate, how do I do this?

 

Thanks for any assistance.

2 REPLIES 2
v-huizhn-msft
Employee
Employee

Hi @Anonymous,

Besides @Phil_Seamark posted, you can separate the Year, for instance, split 20152016 to 2015 and 2016.

Please click Split Column on home page, you will get the following dialogue. You can split 4 characters as far left.

1.PNG


If you have other issues, please feel free to ask.

Best Regards,
Angelia

Phil_Seamark
Employee
Employee

Hi @Anonymous

 

If it were me, I would try and combine the fact/transaction data together into one table if that's practical.  The Query Editor is partucularly useful at helping with that (see Append), but you can do this in DAX as well.

 

You can have a separate Profile table to be tidy but it's not essential (I would keep it separate).

 

Convert your YEAR column to Date time and also add a DATE table.  Make sure the joined columns are using Date Type format


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.