Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

Relationship with Dates

Power bi 3.PNGIn 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

Power bi test.PNGPower bi 2.PNG

10 REPLIES 10
GilesWalker
Skilled Sharer
Skilled Sharer

@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
Not applicable

@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.

 

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

@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
Not applicable

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

Power bi test.PNGPower bi 2.PNG


@Anonymous wrote:

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

Power bi test.PNGPower bi 2.PNG


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

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

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
Not applicable

@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
Not applicable

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.