Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How can I use variables as arguments of the function Table.ExpandRecordColumn ?

Hello,

I am retrieving the table alm_asset from servicenow via its API in Power BI.

It works fine and I am trying now to parameterize as many thing as possible, like the list of columns I want to retrieve.

I had a step like this :

 

= Table.ExpandRecordColumn(#"result developped1", "result", {"parent", "skip_sync", "residual_date", "residual", "sys_updated_on", "request_line", "sys_updated_by", "u_delivery_type", "due_in", "model_category", "sys_created_on", "sys_domain", "disposal_reason", "model", "install_date", "gl_account", "invoice_number", "u_po_number", "sys_created_by", "warranty_expiration", "asset_tag", "depreciated_amount", "substatus", "pre_allocated", "owned_by", "checked_out", "display_name", "asset_function", "u_description", "delivery_date", "retirement_date", "beneficiary", "install_status", "cost_center", "supported_by", "assigned", "life_cycle_stage", "purchase_date", "work_notes", "managed_by", "u_date_de_commande", "sys_class_name", "sys_id", "po_number", "stockroom", "checked_in", "life_cycle_stage_status", "resale_price", "vendor", "company", "retired", "justification", "department", "u_loan_start_date", "u_type", "expenditure_type", "u_derogation", "assigned_to", "depreciation_date", "old_status", "comments", "cost", "quantity", "acquisition_method", "ci", "u_loan_end_date", "sys_mod_count", "u_unit_price", "old_substatus", "serial_number", "sys_tags", "order_date", "support_group", "reserved_for", "due", "location", "lease_id", "salvage_value"}, {"parent", "skip_sync", "residual_date", "residual", "sys_updated_on", "request_line", "sys_updated_by", "u_delivery_type", "due_in", "model_category", "sys_created_on", "sys_domain", "disposal_reason", "model", "install_date", "gl_account", "invoice_number", "u_po_number", "sys_created_by", "warranty_expiration", "asset_tag", "depreciated_amount", "substatus", "pre_allocated", "owned_by", "checked_out", "display_name", "asset_function", "u_description", "delivery_date", "retirement_date", "beneficiary", "install_status", "cost_center", "supported_by", "assigned", "life_cycle_stage", "purchase_date", "work_notes", "managed_by", "u_date_de_commande", "sys_class_name", "sys_id", "po_number", "stockroom", "checked_in", "life_cycle_stage_status", "resale_price", "vendor", "company", "retired", "justification", "department", "u_loan_start_date", "u_type", "expenditure_type", "u_derogation", "assigned_to", "depreciation_date", "old_status", "comments", "cost", "quantity", "acquisition_method", "ci", "u_loan_end_date", "sys_mod_count", "u_unit_price", "old_substatus", "serial_number", "sys_tags", "order_date", "support_group", "reserved_for", "due", "location", "lease_id", "salvage_value"})

 

And now I would expect it to work like this :

 

...
columnsList = "parent, skip_sync, residual_date, residual, sys_updated_on, request_line, sys_updated_by, u_delivery_type, due_in, model_category, sys_created_on, sys_domain, disposal_reason, model, install_date, gl_account, invoice_number, u_po_number, sys_created_by, warranty_expiration, asset_tag, depreciated_amount, substatus, pre_allocated, owned_by, checked_out, display_name, asset_function, u_description, delivery_date, retirement_date, beneficiary, install_status, cost_center, supported_by, assigned, life_cycle_stage, purchase_date, work_notes, managed_by, u_date_de_commande, sys_class_name, sys_id, po_number, stockroom, checked_in, life_cycle_stage_status, resale_price, vendor, company, retired, justification, department, u_loan_start_date, u_type, expenditure_type, u_derogation, assigned_to, depreciation_date, old_status, comments, cost, quantity, acquisition_method, ci, u_loan_end_date, sys_mod_count, u_unit_price, old_substatus, serial_number, sys_tags, order_date, support_group, reserved_for, due, location, lease_id, salvage_value",
...
#"result developped2" = Table.ExpandRecordColumn(#"result developped1", "result", Text.Split(columnsList, ","), Text.Split(columnsList, ",")),
#"model_category développed" = Table.ExpandRecordColumn(#"result developped2", "model_category", {"display_value"}, {"model_category.display_value"}),
...

 

But instead, in the step #"result developped2" all my columns are there but they contain null values.

And in the step #"model_category developped", I have an error message saying it cannot find the column "model_category" (when I do see it in the previous step (eventhough it has null value).

Do you know what I am missing please ?

Regards,

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    I have found out the issue. I hadn't removed the space after each comma in my variable columnsList.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I have found out the issue. I hadn't removed the space after each comma in my variable columnsList.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    The whole code is like this :

    let
    SnowInstance = "nameOfMySnowInstance",
	SnowTable = "alm_asset",
    filterQuery = "model_category=ff3edd13d1233000817c9acbb7f994e3^ORmodel_category=b7d5bc14c3031000b959fd251eba8fd0",
	columnsList = "parent,skip_sync,residual_date,residual,sys_updated_on,request_line,sys_updated_by,u_delivery_type,due_in,model_category,sys_created_on,sys_domain,disposal_reason,model,install_date,gl_account,invoice_number,u_po_number,sys_created_by,warranty_expiration,asset_tag,depreciated_amount,substatus,pre_allocated,owned_by,checked_out,display_name,asset_function,u_description,delivery_date,retirement_date,beneficiary,install_status,cost_center,supported_by,assigned,life_cycle_stage,purchase_date,work_notes,managed_by,u_date_de_commande,sys_class_name,sys_id,po_number,stockroom,checked_in,life_cycle_stage_status,resale_price,vendor,company,retired,justification,department,u_loan_start_date,u_type,expenditure_type,u_derogation,assigned_to,depreciation_date,old_status,comments,cost,quantity,acquisition_method,ci,u_loan_end_date,sys_mod_count,u_unit_price,old_substatus,serial_number,sys_tags,order_date,support_group,reserved_for,due,location,lease_id,salvage_value",
    sysparm_limit = 10000,
    NbTotalRows = Int64.From(Record.ToList(Record.ToList(Record.ToList(Json.Document(Web.Contents("https://" & SnowInstance & ".service-now.com/api/now/stats/" & SnowTable & "?sysparm_count=true&sysparm_query=" & filterQuery))){0}){0}){0}),
    PartieEntiere = Number.IntegerDivide(NbTotalRows, sysparm_limit),
    Modulo = Number.Mod(NbTotalRows, sysparm_limit),
    NbTotalOffset = if Modulo > 0 then PartieEntiere else PartieEntiere - 1,
    CreationTable = Table.FromList({0..NbTotalOffset}, Splitter.SplitByNothing(), {"Offset"}, null, ExtraValues.Error),
    #"Colonne multipliée" = Table.TransformColumns(CreationTable, {{"Offset", each _ * sysparm_limit, type number}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Colonne multipliée", "Personnalisé", each Json.Document(Web.Contents("https://" & SnowInstance & ".service-now.com/api/now/table/" & SnowTable & "?sysparm_display_value=true&sysparm_limit=" & Text.From(sysparm_limit) & "&sysparm_offset=" & Text.From([Offset]) & "&sysparm_query=" & filterQuery & "&sysparm_fields=" & columnsList))),
    #"Personnalisé développé" = Table.ExpandRecordColumn(#"Personnalisée ajoutée", "Personnalisé", {"result"}, {"result"}),
    #"result développé1" = Table.ExpandListColumn(#"Personnalisé développé", "result"),
    #"result développé2" = Table.ExpandRecordColumn(#"result développé1", "result", Text.Split(columnsList, ","), Text.Split(columnsList, ",")),
    #"model_category développé" = Table.ExpandRecordColumn(#"result développé2", "model_category", {"display_value"}, {"model_category.display_value"}),
    #"model développé" = Table.ExpandRecordColumn(#"model_category développé", "model", {"display_value"}, {"model.display_value"}),
    #"vendor développé" = Table.ExpandRecordColumn(#"model développé", "vendor", {"display_value"}, {"vendor.display_value"}),
    #"department développé" = Table.ExpandRecordColumn(#"vendor développé", "department", {"display_value"}, {"department.display_value"}),
    #"Erreurs remplacées" = Table.ReplaceErrorValues(#"department développé", {{"vendor.display_value", ""}}),
    #"Erreurs remplacées1" = Table.ReplaceErrorValues(#"Erreurs remplacées", {{"department.display_value", ""}}),
    #"location développé" = Table.ExpandRecordColumn(#"Erreurs remplacées1", "location", {"display_value"}, {"location.display_value"}),
    #"Erreurs remplacées2" = Table.ReplaceErrorValues(#"location développé", {{"location.display_value", ""}}),
    #"u_type développé" = Table.ExpandRecordColumn(#"Erreurs remplacées2", "u_type", {"display_value"}, {"u_type.display_value"}),
    #"Erreurs remplacées3" = Table.ReplaceErrorValues(#"u_type développé", {{"u_type.display_value", ""}}),
    #"Index ajouté" = Table.AddIndexColumn(#"Erreurs remplacées3", "Index", 1, 1, Int64.Type),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Index ajouté",{"Offset"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"parent", type any}, {"skip_sync", type text}, {"residual_date", type any}, {"residual", Currency.Type}, {"sys_updated_on", type datetime}, {"request_line", type any}, {"sys_updated_by", type text}, {"u_delivery_type", type any}, {"due_in", type any}, {"sys_created_on", type datetime}, {"disposal_reason", type any}, {"install_date", type datetime}, {"gl_account", type any}, {"invoice_number", type any}, {"u_po_number", type text}, {"sys_created_by", type text}, {"warranty_expiration", type any}, {"asset_tag", type text}, {"depreciated_amount", Currency.Type}, {"substatus", type text}, {"pre_allocated", type text}, {"owned_by", type any}, {"checked_out", type any}, {"display_name", type text}, {"asset_function", type any}, {"u_description", type text}, {"delivery_date", type any}, {"retirement_date", type any}, {"beneficiary", type any}, {"install_status", type text}, {"cost_center", type any}, {"supported_by", type any}, {"assigned", type datetime}, {"life_cycle_stage", type any}, {"purchase_date", type any}, {"work_notes", type text}, {"managed_by", type any}, {"u_date_de_commande", type date}, {"sys_class_name", type text}, {"sys_id", type text}, {"po_number", type any}, {"stockroom", type any}, {"checked_in", type any}, {"life_cycle_stage_status", type any}, {"resale_price", Currency.Type}, {"company", type any}, {"retired", type any}, {"justification", type any}, {"u_loan_start_date", type any}, {"expenditure_type", type any}, {"u_derogation", type text}, {"assigned_to", type any}, {"depreciation_date", type any}, {"old_status", type any}, {"comments", type text}, {"cost", Currency.Type}, {"quantity", Int64.Type}, {"acquisition_method", type text}, {"ci", type any}, {"u_loan_end_date", type any}, {"sys_mod_count", Int64.Type}, {"u_unit_price", Currency.Type}, {"old_substatus", type any}, {"serial_number", type text}, {"sys_tags", type any}, {"order_date", type any}, {"support_group", type any}, {"reserved_for", type any}, {"due", type any}, {"lease_id", type any}, {"salvage_value", Currency.Type}}),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Type modifié",{"asset_tag", "model_category.display_value", "model.display_value", "u_type.display_value"})
in
    #"Autres colonnes supprimées"