How to Expand Record Column Conditionally

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.