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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Help with DAX

Hi, I am looking to create a new column on an existing table but I'm not sure of the best way to proceed. My data is as the first table, I want to add the column 'New list name' to the table as the second table at each change of Transaction ID.

So I need to replace the value '(not set)' with the correct list name dependent on the Transaction ID. Thanks

First table

Transaction IDProduct list nameProduct SKUQuantity
02-140885Broadband Productp3-fttp301
02-140885Broadband Productp3-2501
02-140885(not set)installation251
02-140887Broadbandp3-fttp311
02-140887Broadbandp31
02-140887(not set)phone1

Second table

Transaction IDProduct list nameProduct SKUQuantityNew list name
02-140885Broadband Productp3-fttp301Broadband Product
02-140885Broadband Productp3-2501Broadband Product
02-140885(not set)installation251Broadband Product
02-140887Broadbandp3-fttp311Broadband
02-140887Broadbandp31Broadband
02-140887(not set)phone1Broadband
1 ACCEPTED SOLUTION

If you're actually replacing/editing data in the data set, the best way is to use Power Query.  DAX is for analyzing the data afterwards. 

 

In the query editor, you can use replace to find all instances of "not set" and replace them with null values.  Then you can order the data as needed and fill down to fix the Product List Name.   If you want the second column instead of fixing the original, then copy the column and do the same thing.

 

Note that the fill down has potential to give you funky results if the first item when ordered by Transaction ID is a blank.

View solution in original post

3 REPLIES 3
pablopsoto
Regular Visitor

In the query editor replace values of (not set) to "null" and then in the transform tab use fill Down on the desired column.

 

Please mark this as a solution if it answers your question. 🙂

HotChilli
Super User
Super User

Does it have to be DAX? Would a simple 'Fill Down' in Power Query editor meet the requirement?

If you're actually replacing/editing data in the data set, the best way is to use Power Query.  DAX is for analyzing the data afterwards. 

 

In the query editor, you can use replace to find all instances of "not set" and replace them with null values.  Then you can order the data as needed and fill down to fix the Product List Name.   If you want the second column instead of fixing the original, then copy the column and do the same thing.

 

Note that the fill down has potential to give you funky results if the first item when ordered by Transaction ID is a blank.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.