In a table visualisation, any value in columns to the right of any null value are now also null.

[Anonymous]

I have two tables with a many to one relationship. In a table visualisation I want to use data from each table. However when I drop in a vaue from the many table (and select 'show null values') it works as expected (on the column and any columns to the left of it), however, any value in columns to the right of any null value are now also null. Really don't know why! Can anyone assist please?

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Sorry for our unclear explanation, we will try to clarify this behaviour of table visual:

For the below table visual, when add a Qual_Id column in the field, it will show all the value from 'Courses'[Qual_Id], then we add a Qual_Code column, it will get all the Qual_Code value where the Qual_Id = 2(For example the third rows), it will be B2, the Qual_Name is the same, but when you add a Launch_Date, it will search the value in 'ProductLaunchSchedule'[Launch_Date] where 

'Courses'[Qual_Id] = 2 && 'Courses'[Qual_Code] = B2 && 'Courses'[Qual_Name] = BBBB, the 'ProductLaunchSchedule'[Launch_Date] does not have any matched value, so it shows blank, it is normal, but when we put the NumberOfUnits column into the table, it will show the possible value where 'Courses'[Qual_Id] = 2 && 'Courses'[Qual_Code] = B2 && 'Courses'[Qual_Name] = BBBB && 'ProductLaunchSchedule' = Blank(), it can not find any possible value, so does the following column.

We think the most effective way is put the Launch_Date at the last or comple the ProductLaunchSchedule Table even a blank record:

We can use a calculated column to create this table:

Union = UNION('ProductLaunchSchedule',ADDCOLUMNS(EXCEPT(DISTINCT('Courses'[Qual_Id]),DISTINCT('ProductLaunchSchedule'[Qual_Id])),"Location",BLANK(),"Contact",BLANK(),"Launch_Date",BLANK()))

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Sorry for our mistake, the union is a calculated table instead of a calculated column:

Union =
UNION (
    'ProductLaunchSchedule',
    ADDCOLUMNS (
        EXCEPT (
            DISTINCT ( 'Courses'[Qual_Id] ),
            DISTINCT ( 'ProductLaunchSchedule'[Qual_Id] )
        ),
        "Location", BLANK (),
        "Contact", BLANK (),
        "Launch_Date", BLANK ()
    )
)

We just need to get  all the Qual ID that  Coures table have but Product table does not have, that was the first part of this formula:

EXCEPT (
            DISTINCT ( 'Courses'[Qual_Id] ),
            DISTINCT ( 'ProductLaunchSchedule'[Qual_Id] )
        )

Then we add column that exist in product table with blank value so that we can use union function

 ADDCOLUMNS (
        EXCEPT (
            DISTINCT ( 'Courses'[Qual_Id] ),
            DISTINCT ( 'ProductLaunchSchedule'[Qual_Id] )
        ),
        "Location", BLANK (),
        "Contact", BLANK (),
        "Launch_Date", BLANK ()
    )

At last, we use the union function to combine two tables as a new table, then we can use it in table visual and hide the origin one. We can also do it in the Power Query Editor, but consider there are only hundred records, creating a calculated table will be more effective.

Best regards,

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

Heres a better illustration of the issue and a link to the pbix file it was taken from. Hope that explains it better than before.

https://www.dropbox.com/s/smcc8pp80ka87lw/mclarke_sample%20pbix%20file%20for%20PBI%20Help_ManyToOne.... 

Thanks for your assistance with this

[v-lid-msft]

Hi @Anonymous ,

We think it may because the last two columns are filtered by the previous column, When the Date is blank, there is no matched number and manager in the Courses table, so they show blank too.

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Sorry for our unclear explanation, we will try to clarify this behaviour of table visual:

For the below table visual, when add a Qual_Id column in the field, it will show all the value from 'Courses'[Qual_Id], then we add a Qual_Code column, it will get all the Qual_Code value where the Qual_Id = 2(For example the third rows), it will be B2, the Qual_Name is the same, but when you add a Launch_Date, it will search the value in 'ProductLaunchSchedule'[Launch_Date] where 

'Courses'[Qual_Id] = 2 && 'Courses'[Qual_Code] = B2 && 'Courses'[Qual_Name] = BBBB, the 'ProductLaunchSchedule'[Launch_Date] does not have any matched value, so it shows blank, it is normal, but when we put the NumberOfUnits column into the table, it will show the possible value where 'Courses'[Qual_Id] = 2 && 'Courses'[Qual_Code] = B2 && 'Courses'[Qual_Name] = BBBB && 'ProductLaunchSchedule' = Blank(), it can not find any possible value, so does the following column.

We think the most effective way is put the Launch_Date at the last or comple the ProductLaunchSchedule Table even a blank record:

We can use a calculated column to create this table:

Union = UNION('ProductLaunchSchedule',ADDCOLUMNS(EXCEPT(DISTINCT('Courses'[Qual_Id]),DISTINCT('ProductLaunchSchedule'[Qual_Id])),"Location",BLANK(),"Contact",BLANK(),"Launch_Date",BLANK()))

Best regards,

[Anonymous]

Hi Dong,

thanks for your assistance with this. I have marked it as 'Solved' as I was able to re-produce the desired resultant table by creating a table as you showed me. So that's great, thanks. However, I could not grasp how I could create the same table by creating a calculated column 'Union' in your example.  Never the less, I am now trying to implement it in my real data set. 

My 'Courses' data has 800+ records and my Product launch data has 100+ records. I get the principal that I need to have every unique Qual_Id unioned with the Product launch data and with null values were appropriate. Where is the best place to do this and how? Is the calculated 'Union' column the answer? If so how do I apply it?

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Sorry for our mistake, the union is a calculated table instead of a calculated column:

Union =
UNION (
    'ProductLaunchSchedule',
    ADDCOLUMNS (
        EXCEPT (
            DISTINCT ( 'Courses'[Qual_Id] ),
            DISTINCT ( 'ProductLaunchSchedule'[Qual_Id] )
        ),
        "Location", BLANK (),
        "Contact", BLANK (),
        "Launch_Date", BLANK ()
    )
)

We just need to get  all the Qual ID that  Coures table have but Product table does not have, that was the first part of this formula:

EXCEPT (
            DISTINCT ( 'Courses'[Qual_Id] ),
            DISTINCT ( 'ProductLaunchSchedule'[Qual_Id] )
        )

Then we add column that exist in product table with blank value so that we can use union function

 ADDCOLUMNS (
        EXCEPT (
            DISTINCT ( 'Courses'[Qual_Id] ),
            DISTINCT ( 'ProductLaunchSchedule'[Qual_Id] )
        ),
        "Location", BLANK (),
        "Contact", BLANK (),
        "Launch_Date", BLANK ()
    )

At last, we use the union function to combine two tables as a new table, then we can use it in table visual and hide the origin one. We can also do it in the Power Query Editor, but consider there are only hundred records, creating a calculated table will be more effective.

Best regards,

[Anonymous]

Hi Dong,

I got this working as per your code. The problem now is that the date comes through as a measure and I can't seem to change to date format? The ribbon Formating Date option is grayed out and The FORMAT(BLANK (),"dd/mm/yyyy") I tried does not work. Can you assist with this please? 

For other like situations can you give me some directions how to do this in the Power Query Editor?

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can change it to the date type by keeping it as blank() such as following:

Here is the solution using Power Query, we add four extra steps in the product table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQquTMlLrQQyjAyMDHQNDIEIyHHKzMlRitWJVjIGcnxTc5LyS4vyUuGqjCGqnDOKMovByrCbZATkOOYk5inFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Qual_Id = _t, Location = _t, Launch_Date = _t, Contact = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Qual_Id", Int64.Type}, {"Location", type text}, {"Launch_Date", type date}, {"Contact", type text}}),
    
    /** 
    * Please Change the #"Changed Type" to your last step name in Product Table
    * Please Change the Courses to the name of your Courses Table
    * Please Change the Qual_ID to the name of the ID Column
    **/
    Distinct_ID_In_Product = Table.Distinct(Table.SelectColumns(#"Changed Type",{"Qual_Id"})),
    Distinct_ID_In_Courses = Table.Distinct(Table.SelectColumns(Courses,{"Qual_Id"})),
    MissingID = Table.NestedJoin(Distinct_ID_In_Product, {"Qual_Id"}, Distinct_ID_In_Courses, {"Qual_Id"}, "Courses", JoinKind.RightAnti){0}[Courses],
    
    /** 
    * Please Change the #"Changed Type" to your last step name in Product Table
    **/
    UnionTable = Table.Combine({#"Changed Type",MissingID})
in
    UnionTable

Best regards,

[Anonymous]

Thanks Dong,

Thanks, stupidly I wasn't using the date format correctly! Thanks for the Power query solution, I'll give it a go when I get a chance.

I did some more work in with the calculated Table code but had issues with the Union (number and ordering of columns). I finaly solved this by adding a SELECTCOLUMNS to the first union table thereby enforcing the number and order of columns. Works a treat.

_Product Launch Schedule Union = UNION (
    SELECTCOLUMNS(
        'Product Launch Schedule',
        "Qual_Id", 'Product Launch Schedule'[Qual_Id],
        "Index", 'Product Launch Schedule'[Index],
        "SkillsPoint", 'Product Launch Schedule'[SkillsPoint],
        "Product", 'Product Launch Schedule'[Product],
        "Product Status", 'Product Launch Schedule'[Product Status],
        "Date", 'Product Launch Schedule'[Date],
        "Product Event", 'Product Launch Schedule'[Product Event],
        "BookingLink", 'Product Launch Schedule'[BookingLink],
        "Time", 'Product Launch Schedule'[Time],
        "Location + Skype", 'Product Launch Schedule'[Location + Skype]
    ),
    ADDCOLUMNS (
        EXCEPT (
            DISTINCT ( 'Course Master Data'[Qual_Id] ),
            DISTINCT ( 'Product Launch Schedule'[Qual_Id])
        ),
"Index", BLANK (),
"SkillsPoint", BLANK (),
"Product", BLANK (),
"Product Status", BLANK (),
"Date", BLANK (),
"Product Event", BLANK (),
"BookingLink", BLANK (),
"Time", BLANK (),
"Location + Skype", BLANK ()

    )
)

[Anonymous]

Thanks so much, that makes sense now! Great explanation cheers!

[Anonymous]

Can you please tell me how to un-filter them?

[Anonymous]

I do not have any filters on the visualisation or the page. How do I stop this behavoir and show the data that is hidden?

[Anonymous]

Thanks for your reply, sorry don't have the option to upload or share through my company's One Drive. Heres a screen shot from the pbix file though.

If you need the file please advise how to send it to you