Help Creating A New Table

Question

Hi,&nbsp;I have a feeling this is pretty basic but can't find instructions on how to do it!&nbsp;I have a dashboard set up that pulls data from several data files. &nbsp;All data sources contain a date field. &nbsp;I have added two columns to each data table, one which converts the date into a day number (so I can apply a filter to just display weekly data from a Sunday), the other contains the number of days since the most recent date in the table (so I can display the last 7 days of data). &nbsp;This seems inefficient and I'd like to create a table that takes the date column from one of the sources, adds the two columns described above and then all the other tables look up to this table using the date field. &nbsp;Could someone help me to do this please?&nbsp;Many thanks,&nbsp;&nbsp;Simon

aktripathi2506 · Answer

Senario is not very clear to me but if you want to display weekly data from sunday you can add a new column with following formula:

weeknum(date,1) to begine week with Sunday

weeknum(date,2) to begine week with Monday

simonjacobs · Answer

Thanks for the reply, however displaying the data's not the issue. I'm able to filter the data to just show data from Sundays. what I want is to create a table that has a date column - taken from one of the exisiting tables so the start and end dates correspond - plus the two calculated columns. I'm fine with the calculations that need to go into the columns, I'm just not sure how to create the table.

cahabadata · Answer

when in data view, use the Modeling tab in the ribbon, select 'New Table'

this will open the formula bar just below the ribbon where you can define your new table i.e.

NewTable = Table1 [DateField]

and this will create that table for you.... if Table 1's date field has repeating dates and you want just unique dates then wrap this with VALUES ( )

v-micsh-msft · Answer

Hi Simon,&nbsp;I think here you would like to create a reference date table based on the date columns in other tables, right?Below is a thread talking about how to generate the date table, check and see if it would meet your requirements:Possible Tip/Trick: Dynamic Date Dimension TableAfter creating this date table, under relationship View, create table relationships between this date table and the other tables:Create and manage relationships in Power BI DesktopThen it should be available to add this one date column in visual with other columns in different tables.If any further help needed, please feel free to post back.Regards

simonjacobs · Answer

Thanks CahabaData, I tried your suggestion but got an error message - "A single value for column 'date' in table 'Facebook - key metrics' cannot be determined. this can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result.' &nbsp;I wrapped the the syntax in 'All ()' and it worked except that it added an extra row to the end of the table that was blank. &nbsp;Do you know why this would be or how I can get rid of it? &nbsp;It causes problems when I add the calculated columns.&nbsp;

Forum Discussion

Help Creating A New Table

6 Replies

Related content

Create new table

Lakehouse Table Generate Create Table

Create rows from 2 tables

Creating Date Tables

Creating a couple of tables