Relationship with Dates

[Anonymous]

In order to have "Age group" for my data set, i've created a help table with "birthday" as unique dates.  But when i try to link it to birthday, i keep telling me it's not unique.  Any1 knows why?  thanks

[GilesWalker]

@Anonymous - I have had something like this happen before with Excel documents. Go into the query editor and in the Birthdays file have a look at the bottom of the table and see if there are any values there. I have foud sometimes there are a couple random rows at the bottom and these cause the issue.

Giles

[Anonymous]

@GilesWalker

any easy way to go to the bottom of the page? as the birthday are covers from 1900 to today. very long list.  thanks.

[GilesWalker]

@Anonymous - you can click on the filter button and see if any thing other than numbers shows up. otherwise just scroll all the way.

[GilesWalker]

@Anonymous have you tried in the table view in the Power BI desktop? On the left hand side there is a button to see the tables, this will allow you to scroll to the bottom.

[Anonymous]

@GilesWalker i'm unable to see the bottom from data view or query view as it's too many rows.

[Eric_Zhang]

---

@Anonymous wrote:

@GilesWalker i'm unable to see the bottom from data view or query view as it's too many rows.

---

@Anonymous

The first picture tells the story, pay attention to its foot.

TABLE: Age Group(42613 rows) COLUMN:Birthday(42612 distinct values)

To find out the duplicates, try to create a new table

new table =
FILTER (
    SUMMARIZE (
        'Age group',
        'Age group'[Birthday],
        "cnt rows", COUNTROWS ( 'Age group' )
    ),
    [cnt rows] > 1
)

[hoggwildd]

I am no expert, in fact I ask for a lot of help on here, but from my expierence I learned to start in excel by formatting the data as a table with headers. Once that is done you can quickly scroll to the bottom of the table using the slide bar on the right. I have tables with 90,000 rows and can get to the bottom in seconds this way.

Once you do that you might find you have blank rows on your table. That happened to me and it took me 2 days to realize that was the problem. Find Duplicates does not count blank rows so it will not appear as a problem.  Delete those blank rows so your table is completely filled in, then refresh data and import it back in again.

Hope that helps! Goodluck,

HW

[SanderBeukers]

Although i really think you should handle this with measures. You can easily calculate an age with DATEDIFF() and evaluate it to determine the corresponding age group.

[Anonymous]

@GilesWalkeri'm unable to see all the values from filters, as it's "limit of 1000 values reached. but looks like it's all dates.

[Anonymous]

any easy way to go to the bottom of the page? as the birthday are covers from 1900 to today. very long list.  thanks.