Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

PowerBI Comparable to DataStruct

I am more used to programming than clicking button in a tool, but the "Microsoft Language" misses for me some essential tools for working.
 

I got a Database which has some Old Conventions of a Primary Key Number which is essential for handling the Data. To ensure that the Convention is the right one I want to check every Element of this Colum with a Table/HashMap or something similiar to check if the Element exists in this table and change it then to a linked Value.

 

Like in a Hashmap...

 

[ValueColum]Compare to Key --> If True --> [ValueColum]= HashmapKey

 

I searched the docs and Internet and sadly didnt find a solution, i thought i got one when I found the "Record" tool from Microsoft but this doesnt work since it can't compare the whole datastruct to a Colum, or may i miss there something?

Need Help

4 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Hi,
    I'm not fully familiar with some of the terminology you use however I think you're looking to ensure every record in one table as a related value in another?

     

    In Power Bi if you create a relationship between the two tables I'm assuming it will form a 1 to many relationship?

     

    There are then various ways to check that referential integrity is intact. 

    Easiest is to add a calculated column to the table on the many side and use RELATED.

     

    Eg
    RelatedId = RELATED ( OneSideTable[Primary Key] )

     

    You can then filter to records where this is not the case.

     

    If I'm missing the point entirely let me know and try and find some common terminology!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hey Guys,

       

      thanks for the answers but they both dont satisfact me.

       

      @bcdobbs I got a convention table with Serial Numbers which have been converted into a new format.

       

      So the table i am including has partial still this old serial numbers in it, they may pop up multiple times, so i can't just match 1 colum to another.

       

      It's more a Check if the number is an old serial number, and IF "true" then i swap it to a assigned new number.

       

      I need the whole "table" i want to use for my charts with the same serial number format, right now i got a mixed format which doesnt sort my data then.

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        This sounds like something that can be done fairly easily in the query editor. Take your data table and merge it with a table that has the old --> new serial mapping, expand the new serial column, and write a conditional column to consolidate the serial number columns.

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    I'm not fully understanding what you're asking either. Can you restate what your goal is without "datastruc" or "HashMap" as those are not commonly used jargon in this community?

     

    It seems like you want to do some kind of comparison and/or update of some columns/tables but it isn't clear to me what you're comparing and why.