Forum Discussion

TA_'s avatar
TA_
Regular Visitor
11 months ago
Solved

Column exchange with a calculated column?

Hello, I am stuck with the following problem; I need to edit a column cointaining unique keys. Unfortunately, a number of keys have changed within the last year or so. There is an operation to compa...
Need Help
  • MFelix's avatar
    MFelix
    11 months ago

    Hi TA_ ,

     

    What are the columns you are using for the merge? Looking at the DAX code you should do the merge on the Info Table and match the Map_WE_ME[OldKey] with the Info[ID_WE_ME] then expand the Map_WE_ME[NewKey] on the resulting column. After that Replace the null by the  Info[ID_WE_ME]

     
    How are you setting the merge on the tables? Is on the info table or on the Map table?
     
    Can you share that specific line of code please.
TA_'s avatar
TA_
Regular Visitor
11 months ago

Hey Felix,

thank you for your fast response 😀.

To give you more context, I am comparing different buildings with each other. These are seperated in WE, but each WE has different ME in it. And to create a unique key for each specific part of a building, there is a key column ID_WE_ME that cointains the correct combination of the specific unit in Format like this: 12345/01.

Since a few Objectives changed their WE/ME, I uploaded a table cointaining a mapping for these changes (MAP_WE_ME), where you can read for example that 12345/01 is from the start of 2025 56789/01. Then I calculated a new column (ID_ME_WE#) within my original table (Info) containing the needed information about the units, aswell as the key ID_WE_ME. My dax code looks like this: 

ID_WE_ME# = 
COALESCE(
    LOOKUPVALUE(
        Map_WE_ME[NewKey],
        Map_WE_ME[OldKey], Info[ID_WE_ME]
    ),
    Info[ID_WE_ME]
)
To specific the procedure, I compare the same units from year 2024 to 2025.
I do not know if it is possible to recreate the ID_WE_ME# within the powerquery, if possible it would kinda solve all my issues.
  • MFelix's avatar
    MFelix
    Super User
    11 months ago

    Hi TA_ ,

     

    Since you have the information in another table you are able to do this column in power query, uysing the merge of the two tables.

     

    Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.