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
davi
Frequent Visitor

Replace null values from multiple columns with values from other columns in place

I have a table with a level structure with some null values on upper levels and I wanted to replace those empty records with the value from the first previous level that is not null. Conceptually I thought that  using Table.TransformColumns would work if it where like:

StepA = Table.TransformColumns(#"PreviousStep", {{"A1", each if _ = null then [A] else _}, {"A2", each if _ = null then [A1] else _},
{"B1", each if _ = null then [B] else _}, {"B2", each if _ = null then [B1] else _}})

But it shows me the error "Expression Error: We cannot apply field access to the type text", which led me to think that the function Table.TransformColumns doesn't allow me to use other columns on the expressions.

PowerQuery.png

 

 

 

 

I know that this could be done by creating custom columns and deleting the old ones and I realize that I could also do it with Table.ReplaceValue, but on my real scenario I have 8 levels, and those alternatives would make me have to create multiple steps, which would be pretty confusing. I would like an alternative that would allow me to solve this problem with one step overall or at max one step per level. Is it possible?

2 ACCEPTED SOLUTIONS
AlienSx
Super User
Super User

Hi, @davi 

use Table.TransformRows instead. It will give you list of records in the end but you can wrap the whole step with Table.FromRows to get your table back. 

View solution in original post

davi
Frequent Visitor

Thanks @AlienSx ! I had to create one step per level like bellow and it worked:

Table.FromRecords(Table.TransformRows(#PreviousStep,
(r) => Record.TransformFields(r,
{{"A1", each if _ = null then r[#"A"] else _},
{"B1", each if _ = null then r[#"B"] else _}})))

View solution in original post

3 REPLIES 3
AlienSx
Super User
Super User

Hi, @davi 

use Table.TransformRows instead. It will give you list of records in the end but you can wrap the whole step with Table.FromRows to get your table back. 

davi
Frequent Visitor

Thanks @AlienSx ! I had to create one step per level like bellow and it worked:

Table.FromRecords(Table.TransformRows(#PreviousStep,
(r) => Record.TransformFields(r,
{{"A1", each if _ = null then r[#"A"] else _},
{"B1", each if _ = null then r[#"B"] else _}})))

Neat!

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors