Forum Discussion

mmurphy_asg's avatar
mmurphy_asg
New Member
2 years ago
Solved

How to Expand Record Column Conditionally

I have a Record column called ContractData where i need to conditionally expand the column.  The record contain all six fields but depending on the value of the ContractTypeID column, I only want to expand two of the 6 values to the IsIssued and IsEligible columns 

I wrote this statement.

 

Table.ExpandRecordColumn(#"Reordered columns 1", "ContractData", each if [ContractTypeID] = 1 then {"VSC_SaleCount", "VscIsEligible"} else if [ContractTypeID] = 2 then {"GAP_SaleCount", "GapIsEligible"} else {"MS_SaleCount", "MsIsEligible"}, {"IsIssued", "IsEligible"})

 

I get an error "Expression.Error: We cannot convert a value of type Function to type List."

 

I assume this is occuring because the each statement is returning a function and not the List determined by the result of the conditional statement.

Any idea how to get this to work?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solution Greg_Deckler offered and i want to offer some more information for user to refer to.

    hello mmurphy_asg , you can create a blank query and put the following code to advanced editor in power query.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hc6pEQAxDMDAXoxD8lXjCTh//ZdwI5NFAlKVKUM+MHAISKgpb6gsGjBwCEio1c2mAQOHgITa3RwaMHAISKjTTf9cGnAISKgr7/0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ContractTypeID = _t, VSC_SaleCount = _t, VscIsEligible = _t, GAP_SaleCount = _t, GapIsEligible = _t, MsIsEligible = _t, MS_SaleCount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ContractTypeID", Int64.Type}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type",{"ContractTypeID", "VSC_SaleCount", "VscIsEligible", "GAP_SaleCount", "GapIsEligible", "MS_SaleCount", "MsIsEligible"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns1", {"ContractTypeID"}, {{"Count",each _,type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let a=if [ContractTypeID]=1 then Table.SelectColumns([Count],{"VSC_SaleCount","VscIsEligible"}) else if [ContractTypeID]=2 then Table.SelectColumns([Count],{"GAP_SaleCount","GapIsEligible"}) else Table.SelectColumns([Count],{"MS_SaleCount","MsIsEligible"}),
b=List.Zip({Table.ColumnNames(a),{"IsIssued", "IsEligible"}})
in Table.RenameColumns(a,b)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"IsIssued", "IsEligible"}, {"IsIssued", "IsEligible"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"})
in
    #"Removed Columns"

    Output

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    mmurphy_asg I would probably expand all six columns and then create 2 new columns that perform the if then else logic. Then remove the six columns.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    Thanks for the solution Greg_Deckler offered and i want to offer some more information for user to refer to.

    hello mmurphy_asg , you can create a blank query and put the following code to advanced editor in power query.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hc6pEQAxDMDAXoxD8lXjCTh//ZdwI5NFAlKVKUM+MHAISKgpb6gsGjBwCEio1c2mAQOHgITa3RwaMHAISKjTTf9cGnAISKgr7/0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ContractTypeID = _t, VSC_SaleCount = _t, VscIsEligible = _t, GAP_SaleCount = _t, GapIsEligible = _t, MsIsEligible = _t, MS_SaleCount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ContractTypeID", Int64.Type}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type",{"ContractTypeID", "VSC_SaleCount", "VscIsEligible", "GAP_SaleCount", "GapIsEligible", "MS_SaleCount", "MsIsEligible"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns1", {"ContractTypeID"}, {{"Count",each _,type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let a=if [ContractTypeID]=1 then Table.SelectColumns([Count],{"VSC_SaleCount","VscIsEligible"}) else if [ContractTypeID]=2 then Table.SelectColumns([Count],{"GAP_SaleCount","GapIsEligible"}) else Table.SelectColumns([Count],{"MS_SaleCount","MsIsEligible"}),
b=List.Zip({Table.ColumnNames(a),{"IsIssued", "IsEligible"}})
in Table.RenameColumns(a,b)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"IsIssued", "IsEligible"}, {"IsIssued", "IsEligible"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"})
in
    #"Removed Columns"

    Output

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.