Column exchange with a calculated column?

[TA_]

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 compare changes of objectives attached to those keys. Therefore, changes of unique keys lead to an exclusion of those objectives from the comparison, since the check-up will not be able to recognize, that two different keys are in fact one single objective.

I have already created a new calculated column with the correct keys. Unfortunately i can not delete the old column and rename the new one, since I used a lookupvalue function directed to the old column.

My Idea was, that i could name swap the columns in the Power Query. What i did not know at the time is, that a calculated column wont show up in the power query. 

I hope there is a clever solution to my problem, except of renaming the used "key Column" in every single calculation of the dashboard.

[v-nmadadi-msft]

Hi @TA_  ,
Thanks for reaching out to the Microsoft fabric community forum.

Since calculated columns don’t appear in Power Query, try using this approach to fix the key column upstream. You can do this by creating a new column in Power Query with the corrected key values instead of handling it in DAX, then removing the old column and renaming the new one to match the original column’s name. This way, all your existing DAX measures that reference the old key will continue working without any additional changes, since the column name is preserved.
If even this approach is not suitable, consider changing the values in the source database if possible.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[TA_]

Hi,

in my case, the mentioned way of recreating a column in powerquery and then merging it with my other table should be the correct way. Unfortunately i do get null values, but I do believe that my data might be corrupted. Anyways, the steps serve my purpose and sooner or later will get me to the point where I need it to be.

Thanks for the advises!

[MFelix]

Hi @TA_ ,

When you received null values it means that you do not have corresponding values on the merge tables on this case you must replace your nulls by the  Info[ID_WE_ME] field that is the second part of the coalesce that you are doing in the DAX formula.

[TA_]

Hi Felix,

unfortunately, the data of the Info table and my reference table do not match at all. I am not certain about the source of error, but since this dashboard was created by another person i might just rebuild the entire project and set everything up in a way i can locate potential errors without guessing what the thought process of my predecessor was.

[MFelix · Accepted solution]

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_]

Hey @MFelix 

this is the target column of my Info table. Each key is a building. There are several cases where two keys are the same building. This happens because those unique keys changed. My goal is to replace the old ones. 

This is my new table (Änderungen_G) containing the information about the changes of ME&WE.

I tried different ging options. In my first attempt I created a new column in Änderungen_G and called it ID_WE_ME with the old values for WE and ME. The next step was to merge queries (starting from my main table/Info) and selecting both ID_ME_WE columns. I chose Left Outer and no fuzzy matching. What I thought would happen is the option to expand my new merged column so i can see MEneu and WEneu, so i could create a new column ID_WE_ME with these and rename the outdated one. Sadly I received null values.

My second attempt was basicly the same merging but I chose both ME and WE from each table without creating a new column. Results were the same tho.

What I still do not understand isthe following; If I search for combination based on the Änderungen_G table within the Info table (in the table view of power BI or the excel table) I do get results. If I try the same thing within the power query editor I don´t get any results at all. I have no clue why, but this should be the reason I can not get any results of my merging, since there are no corresponding values. 

[TA_]

I did the same steps as mentioned before with a clean copy of the data and somehow everything went well. I merged the columns of my assisting table to receive the old keys and the same for the new keys. Then the queries are merged based on the old keys and the new created column has all the new keys when expanded. After renaming the columns the dashboard was updated and the new keys were apllied.

Thanks for the assistance!

[TA_]

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]

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.

[MFelix]

Hi @TA_ ,

I don't know how you are calculating the new column but I would redo that column in Power Query that way you can then rename or delete the previous column if not necessary.

Another question is that if you are doing a Lookupvalue function to generate a new column in another table I would also pass this calcution to the Power Query using the Merge option between queries this would allow more flexibility and ensure consistancy.

Can you please give more context on what you are calculating and how?