Forum Discussion

Nazeem's avatar
Nazeem
Frequent Visitor
9 years ago
Solved

Manage relationships

Hi,   I have created a date table in Power BI and want to create a relartionships between mu date table and my tabler for sales data. I managed to create the date table but when I tried to create a...
Show and Tell
  • Anonymous's avatar
    Anonymous
    9 years ago

    You could either filter that row(s) or replace the value with some other date to make it work. Both these options are done in the query editor before you load the table. Click on Edit Queries to open the query editor. 

     

     

Nazeem's avatar
Nazeem
Frequent Visitor
9 years ago

I think that the problem is that the column Accrual.period.Month is of "Data Type" text. Anyome knows hot to convert 201602 (text type) to a date type?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    You could create a new column in your Accounting table which will act as key for your relationship to your date table.

     

    DateKey = DATEVALUE(LEFT(Accounting[Accrual Period.Month],4)&"-"&RIGHT([Accrual Period.Month],2)&"-01")

    This will extract the year and month from your existing column and append 01 as the first day of that month. 

     

    Br,
    Magnus

    • Nazeem's avatar
      Nazeem
      Frequent Visitor
      9 years ago

      Thanks for the reply,

       

      I get the following error message when eneterting your code:

       

       

      I think it's because there is a date value in column Accrual Period:Month that is Unknown

       

       

      Any clue how to solve this?

       

      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        You could either filter that row(s) or replace the value with some other date to make it work. Both these options are done in the query editor before you load the table. Click on Edit Queries to open the query editor.