Data Modeling Help

[Anonymous]

Newb here to PowerBi and DAX.

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

Year	OrgID	Value1	Value2
20152016	001001	2	26
20152016	001002	58	6378

Year	OrgID	Value1	Value2
20142015	001001	3	25
20142015	001003	59	6399

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.

[v-huizhn-msft]

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.

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

Best Regards,
Angelia

[Phil_Seamark]

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