cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Conditional transform many dynamic columns based on another columns - feed Record.TransformFields ..

Q : Conditional transform MANY dynamic columns based on another columns - feed Record.TransformFields with generated list

 

I have tried to find solution and tried few thing on my own but I realized that I'm stucked and that I need help
So first I will describe the table and then the problem.

I need to change columns based on some condition and based on comparation of other columns.
In real case there are many (50-100) columns and they often change names. I have solved how to get list of columns that needs to be transformed so
for the purpose of this description I will simplify table and we will assume that columns stay same.

Table1

| Number1 | Number2 | Percent | A | B | C |
|---------|---------|---------|---|---|---|
| | | | | | |
| | | | | | |

I need to change number values in columns A, B, and C if condition is not met, if condition is meet they need to stay as they are.
The condition look like this:

if [Number1] = [Number2] then A else A * [Percent]
if [Number1] = [Number2] then B else B * [Percent]
if [Number1] = [Number2] then C else C * [Percent]

I have used code for specific (in this case 3) number of columns and it worked:
(https://stackoverflow.com/questions/31548135/power-query-transform-a-column-based-on-another-column)

=Table.FromRecords(
    Table.TransformRows(
        Table1,
        (r) => 
        Record.TransformFields(
            r,
            {
                {"Column A", each  if [Number1] = [Number2] then _ else _ * [Percent]},
				{"Column B", each  if [Number1] = [Number2] then _ else _ * [Percent]},
                {"Column C", each  if [Number1] = [Number2] then _ else _ * [Percent]}
            }
        )
    ),
    Value.Type(Table1)
)

 

But as I have many columuns I would like to feed Record.TransformFields with generated list.
So is there a way to use List.Transform to generate List transformOperationsfor Record.TransformFileds?
Something like this:

=Table.FromRecords(
    Table.TransformRows(
        Table1,
        (r) => 
        Record.TransformFields(
            r,
            {
				List.Transform(
					{"A", "B"},
					each "{"""& _ & """" & ", each if [Number1] = [Number2] then _ else _ * [Percent])}"
				)           
            }
        )
    ),
    Value.Type(Table1)
)

I also tried with Table.TransformColumns but it didn't worked:

= Table.TransformColumns(Table1,List.Transform(
					{"A", "B"},
					each "{"""& _ & """" & ", each if [Number1] = [Number2] then _ else _ * [Percent])}"
				)),


I’m guessing that I would need to use List.Accumulate but not figured out yet.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Hello @tstivoj 

 

List.Accumulate is for sure one possibility. Here the M-code that should suit for you

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYkMdQ1MQZQAmoKSBUqwORAkY6xiBlBgZgAkoCVQSCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number1 = _t, Number2 = _t, Percent = _t, A = _t, B = _t, C = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number1", Int64.Type}, {"Number2", Int64.Type}, {"Percent", type number}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    ListOfColumns = {"A", "B", "C"},
    Custom1 = Table.FromRecords(
    Table.TransformRows(
        #"Changed Type",
        (r) => 
        if r[Number1] = r[Number2] then r else List.Accumulate(ListOfColumns, r, (state, current)=> Record.TransformFields(
            state,
            {
                {current, each   _ * r[Percent]}

            }
        ) )), Value.Type(#"Changed Type"))   

in
    Custom1

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

Hello @tstivoj 

 

List.Accumulate is for sure one possibility. Here the M-code that should suit for you

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYkMdQ1MQZQAmoKSBUqwORAkY6xiBlBgZgAkoCVQSCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number1 = _t, Number2 = _t, Percent = _t, A = _t, B = _t, C = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number1", Int64.Type}, {"Number2", Int64.Type}, {"Percent", type number}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    ListOfColumns = {"A", "B", "C"},
    Custom1 = Table.FromRecords(
    Table.TransformRows(
        #"Changed Type",
        (r) => 
        if r[Number1] = r[Number2] then r else List.Accumulate(ListOfColumns, r, (state, current)=> Record.TransformFields(
            state,
            {
                {current, each   _ * r[Percent]}

            }
        ) )), Value.Type(#"Changed Type"))   

in
    Custom1

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

Highlighted
Regular Visitor

Excellent @Jimmy801  that was very fast. Thank you very much. I'm so grateful. I tried it and it works. Have a nice day and weekday

 

Highlighted

 

 

The solution of @Jimmy801 is more elegant (although list.accumulate may have performance limits in some cases), but perhaps this (just as an exercise) is close to what you were looking for

 

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc6xEQAhCETRXogJVtCb68Wx/zaExRkCPsELYG8ZojU6Vi4wkKNlRrM0A1NmbVEH0+Sk7ART5I+gf3SBaapH8tYHJuhc", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number1 = _t, Number2 = _t, Percent = _t, A = _t, B = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Number1", type number}, {"Number2", type number}, {"Percent", type number}, {"A", type number}, {"B", type number}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "pc", each if [Number1]=[Number2] then 1 else [Percent], type number),
    cols=List.RemoveLastN(List.RemoveFirstN(Table.ColumnNames(#"Aggiunta colonna personalizzata"),3),1),
    nCols= List.Count(cols),
       
    tab=Table.FromRecords( Table.TransformRows(#"Aggiunta colonna personalizzata", 
    
    (row)=> Record.TransformFields(row, List.Zip({cols, List.Repeat({(x)=> row[pc]*x},nCols)}) )
    ))
 

in
   tab

 

Highlighted

 

 

 

using Table.CombineColumns ...

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc6xDcAwCAXRXagpvnEcZRfL+68Rc0ROwaO4Aua0Zl7jbeQSoC2vHvTIHgKkx9+3XYAnd3J6CZDcvyx/tkOAJ9dzefsWoK31Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number1 = _t, Number2 = _t, Percent = _t, A = _t, B = _t, C = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Number1", type number}, {"Number2", type number}, {"Percent", type number}, {"A", type number}, {"B", type number}, {"C", type number}}),
    names=Table.ColumnNames(#"Modificato tipo"),
    acp = Table.AddColumn(#"Modificato tipo", "pc", each if [Number1]=[Number2] then 1 else [Percent], type number),
    cols=List.RemoveFirstN(names,3),
    nCols= List.Count(cols),
    f3c=List.Transform({0..2},  each Table.Column(acp,names{_})),   
    #"Merge di colonne" = Table.FromColumns(f3c & List.Zip(Table.CombineColumns(acp,cols,(x)=>List.Transform(List.RemoveLastN(x,1),each _*List.Last(x))&{List.Last(x)} ,"mrg")[mrg]),names)
in
    #"Merge di colonne"

 

Highlighted

Hello, super and adanced users..

 

very interesting, I tried to understand the functions and their nesting, for this I modified the code slightly, to add 1 coloumn, an instance else, and play on the settings.

I understood that r was the recording of the source table for the current line but I have more trouble understanding well "state" and "current".

I think state corresponds to the state of content of the current columns of the record r before its transformation and current , the state of the content of the current column of the record r after application of the function.

 

One of you could confirm me or correct me

 

Philippe Muniesa

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors