Transform a Column

[shuyin]

hi,

I have a table:

A	B
	1
	1
2	3
2	3

and I want to transform column A based on the condition of column B as below:

Transform = Table.TransformColumns(#"Changed Type", {"A", each if [A] = null then [B] else [A]})

but it shows the error:
Expression.Error: We cannot apply field access to the type Number.
Details:
Value=0
Key=A

How can I resolve this? I know we can add new conditional column, and then remove the old one, but it is too redundant.

Thanks

 

[Ken Puls]

Before the step where you add your conditional logic, select the column you are checking and format it as text. That error indicates that it is trying to compare text (A,B,C...) but the column is formatted as numeric.


Sent from my iPhone using Tapatalk

 

[shuyin]

hi Ken,

I tried with that solution, but it shows error:
Expression.Error: We cannot apply field access to the type Text.
Details:
Value=0
Key=A

 

[Owen Auger]

Hi shuyin,

Your problem is similar to one posted on stackoverflow.
Google this and it should come up: "power-query-transform-a-column-based-on-another-column"
(I can't post links on this forum yet )

i.e. you are trying to transform one column based on another column.
But Table.TransformColumns can access only each column in isolation.

The above link has a solution which can be modified for your situation:

Try this:

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

 

[shuyin]

hi Owen,

Thank you for the code. It works!

Could you help me understand a bit about "_" you used there?
I tried with the code:

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

but it doesn't work. I guess "_" represent for each record of the column?

Why can't we use Table.TransformColumns function?. I think there is a way to use that function. It would be more elegant!

Thanks

 

[Ken Puls]

Ah, shoot. Sorry, is misread that from my iPhone, and thought I'd read the HTML table tags correctly. I thought your A and B values were the rows in the columns.

Out of curiosity, how are you building the conditional logic? Are you using the new "conditional column" interface? If you are, that's what is killing you.

Try this:

• Go to Add Column --> Add Custom Column
• Use the following formula:

if [A]=null then [B] else [A]

My guess is that this should do it for you.

 

[shuyin]

hi Ken,

With that approach, we need 3 steps: add new column, remove the old column, and rename the new column.

I am looking for the use of Table.TransformColumns to achieve that in 1 step only.

 

[Ken Puls]

I am looking for the use of Table.TransformColumns to achieve that in 1 step only.

So I have to ask... why? Performance? I'm all about coming up with new ways to do things, but the command isn't built to work that way. As Owen stated above:

i.e. you are trying to transform one column based on another column.
But Table.TransformColumns can access only each column in isolation.

Personally, if I were building this for either myself or a client, I'd choose the method I showed for one very simple reason: it preserves and audit flow in the Applied Steps window. That will make it FAR easier to read/maintain and debug later. But that's just my preference.

 

[Bill Szysz]

Absolutely agree with Ken

With that approach, we need 3 steps: add new column, remove the old column, and rename the new column.

I am looking for the use of Table.TransformColumns to achieve that in 1 step only.

Less number of steps does not mean better performance. (sometimes only)
If you want to use the code wirh r, you have to change the part below

{"A", each if [A] =null then r[B] else [A]}

to

{"A", each if _ =null then r[B] else _}

Alternatively, you can use this code (this is only example of one of the possible ways)

Transform = let
      Add = Table.AddColumn(#"Changed Type", "Custom", each if [A]=null then [B] else [A] ),
      Remove = Table.RemoveColumns(Add, "A"),
      Rename = Table.RenameColumns(Remove, {{"Custom","A"}}),
      Reorder = Table.ReorderColumns(Rename, {"A","B"})
    in
      Reorder

As you can see, this is one step with inner steps. We can write this step also in this way

Transform = Table.ReorderColumns(Table.RenameColumns(Table.RemoveColumns(Table.AddColumn(#"Changed Type", "Custom", each if [A]=null then [B] else [A] ),"A"), {{"Custom","A"}}), {"A","B"})

...many nested functions but this is exatly the same as previous.

Regards

 

[Dan Bliss]

The following gets the same error - "We cannot apply field access to the type Text"


Suppose a column, [filename], text field with values that include a date, such as:
2016-02-09-statements-8345.xlsx
Chase200902Feb09.xlsx
Where the "format" of the embedded date is not consistent over all rows or is unknown to the Power Query programmer ahead of time.
Programming in Power Query is simple if we know the file names always begin with "Chase". If there are 2 fomats, as shown here, then use a filter to separate the "Chase" leading names from those that contain "statements". But how do we program this if we only know the file name contains a date, but we don't know what the delimiters are - such as "statements" or "Chase", or even "-"?


One idea I had was to load [filenames] to a table in Excel, then have User add Start and Length values for each row. Start & Len values then be used in function Text.Middle() to extract the date. My thinking is it would be easier for a human to fill in a table of Left1Start & Left1Len values then try to build M steps that handle a universe of possible date formats. (But maybe there's a simpler way?)


Anyway, so here's what my table looks like for these 2 filenames:


FullFileName Left1Start Left1Len Left2Start Left2Len
2016-02-09-statements-8345.xlsx 0 10 0 0
Chase200902Feb09.xlsx 5 6 14 2


Table.TransformColumns(#"Changed Type", {{"FullFileName", each Text.Middle(_, [Left1Start], [Left1Len])}}) produces the "We cannot apply field access to the type Text" error (Left1Start, etc are type Int64).


Any thoughts?

 

[Pecoflyer]

Please do not hijack threads. Best to create a new one with eventually a link to any relevant threads