Help Creating A New Table

[SimonJacobs]

Hi,

I have a feeling this is pretty basic but can't find instructions on how to do it!

I have a dashboard set up that pulls data from several data files.  All data sources contain a date field.  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).  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.  Could someone help me to do this please?

Many thanks,

Simon

[aktripathi2506]

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]

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.

[v-micsh-msft]

Hi Simon,

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 Table

After 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 Desktop

Then 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

[CahabaData]

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 ( )

[SimonJacobs]

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.'  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.  Do you know why this would be or how I can get rid of it?  It causes problems when I add the calculated columns.

[CahabaData]

I wonder about that error message in that it seems oriented to a measure; and my intention was a new calculated table.

In regard to a blank row in a table; yes that can be dealt with in the Home tab up in the ribbon; with the Query Editor; in that window's ribbon is a Remove Rows option.