Forum Discussion

EricMEwald's avatar
EricMEwald
Regular Visitor
7 years ago

Filtering out data in columns using query editor

Background:

I would like to filter out data im pulling from Adobe Analytics using query editor.

 

Issue:

 

I cannot find a solution to query values only >0 for the revenue column. Any hepl would be much appreciated.

 

{
{Cube.ApplyParameter, "DateRange", {#date(2018, 10, 16), #date(2018, 10, 24)}},
{Cube.ApplyParameter, "Top", {20000, "evar47"}},
{Cube.AddAndExpandDimensionColumn, "DateGranularity", {"year", "month", "day"}, {"Date Granularity.Level 1: Year", "Date Granularity.Level 2: Month", "Date Granularity.Level 3: Day"}},
{Cube.AddAndExpandDimensionColumn, "evar47", {"evar47"}, {"Order ID (v47)"}},
{Cube.AddMeasureColumn, "Revenue", "revenue"}
}),

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi EricMEwald

    You could write this function in the Advanced editor

    Table.SelectRows(table as table, condition as function)

    It returns a table containing only the rows that match a condition.

     

    In your scenario, code is like

    Let 
      Source=***,
      last step=***,
      selectrows=Table.SelectRows(last step, each[revenue]>0)
    in
      selectrows

     

    Best Regards

    Maggie

    • EricMEwald's avatar
      EricMEwald
      Regular Visitor
      7 years ago

      Maggie,

       

      Thank you for the response! Im very new to M, and my knowledge is limited. I have been working in advanced editor, and added some code to modify the date, Is it possilbe you send a snippet of code similar to code i recived for the date modification (below)?

       


      #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Items", {{"Date Granularity.Level 2: Month", type text}, {"Date Granularity.Level 3: Day", type text}, {"Date Granularity.Level 1: Year", type text}}, "en-US"),{"Date Granularity.Level 2: Month", "Date Granularity.Level 3: Day", "Date Granularity.Level 1: Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),

      #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}})

       

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi EricMEwald

        Is this code related to your original question?

        How would you like to modify the date in the code below?

        I'm not clear what purpose you write this code and let me edit.

        Please let me know what is your expected result with code edited based on this.

         

        Best Regards

        Maggie