Forum Discussion

prabhat_m's avatar
prabhat_m
Frequent Visitor
3 years ago

If statmement in Mquery

Hello,

I am using the web connector to extract data from an API into PowerBI

I want to change the applied steps to the extracted data using IF statement in MQUERY.. where the condition will check a parameter value (eg either "recorded" or "value"). How to get this done?

Something highlighted like below

 

let
Source = #"Parent Element (2)",

IF parameter = "recorded" then
{
#"Removed Columns" = Table.RemoveColumns(Source,{"Parent Webid", "Child Element Webid"}),
#"Expanded Data" = Table.ExpandRecordColumn(#"Removed Columns", "Data", {"Items"}, {"Items"}),
#"Expanded Items" = Table.ExpandListColumn(#"Expanded Data", "Items"),
#"Expanded Items1" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {"Name", "Items"}, {"Name", "Items.1"}),
#"Expanded Items.1" = Table.ExpandListColumn(#"Expanded Items1", "Items.1"),
#"Expanded Items.2" = Table.ExpandRecordColumn(#"Expanded Items.1", "Items.1", {"Timestamp", "Value"}, {"Timestamp", "Value"})
in
#"Expanded Items.2"
}

elseif parameter = "value"
{
#"Removed Columns" = Table.RemoveColumns(Source,{"Parent Webid", "Child Element Webid"}),
#"Expanded Data" = Table.ExpandRecordColumn(#"Removed Columns", "Data", {"Items"}, {"Items"}),
#"Expanded Items" = Table.ExpandListColumn(#"Expanded Data", "Items"),
#"Expanded Items1" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {"Name", "Value"}, {"Name", "Value"}),
#"Expanded Value" = Table.ExpandRecordColumn(#"Expanded Items1", "Value", {"Timestamp", "Value"}, {"Timestamp", "Value.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Value",{{"Name", "Attribute"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Timestamp", type datetimezone}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Value.1", "Value"}})
in
#"Renamed Columns1"
}

1 Reply

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion

    let
    Source = #"Parent Element (2)",


    #"Removed Columns" = Table.RemoveColumns(Source,{"Parent Webid", "Child Element Webid"}),
    #"Expanded Data" = Table.ExpandRecordColumn(#"Removed Columns", "Data", {"Items"}, {"Items"}),
    #"Expanded Items" = Table.ExpandListColumn(#"Expanded Data", "Items"),
    #"Expanded Items1" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {"Name", "Items"}, {"Name", "Items.1"}),
    #"Expanded Items.1" = Table.ExpandListColumn(#"Expanded Items1", "Items.1"),
    #"Expanded Items.2" = Table.ExpandRecordColumn(#"Expanded Items.1", "Items.1", {"Timestamp", "Value"}, {"Timestamp", "Value"}),
    #"Expanded Items x" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {"Name", "Value"}, {"Attribute", "Value"}),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Expanded Items x", "Value", {"Timestamp", "Value"}, {"Timestamp", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Value",{{"Timestamp", type datetimezone}}),

    GetResult = IF parameter = "recorded" then #"Expanded Items.2" else #"Changed Type"
    in
    GetResult