Filtering out data in columns using query editor

[EricMEwald]

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"}
}),

[v-juanli-msft]

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]

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]

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

[EricMEwald]

Maggie,

The code is related to mr original question. I shows that exmaple, since it is an example where i would just add the code in advanced query editor.

My expected result would be a snipped of code i could just include in the query editor. In your resposne I do not know what to include after "last step" or "source".

Here is my full code (i put ***** for parts that are identifiable of my organization). If possilbe id like some code i just can copy and paste in.

let
Source = AdobeAnalytics.Cubes([HierarchicalNavigation=true]),
***** = Source{[Name="*****"]}[Data],
***** = *****{[Id="*****"]}[Data],
#"Added Items" = Cube.Transform(*****,
{
{Cube.ApplyParameter, "DateRange", {#date(2018, 10, 15), #date(2018, 10, 26)}},
{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"},
{Cube.AddMeasureColumn, "Shipping Revenue (e54)", "event54"},
{Cube.AddMeasureColumn, "Tax Revenue (e55)", "event55"}
}),
#"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}})

in

#"Changed Type"

[v-juanli-msft]

Hi @EricMEwald

Hi, sorry for my late, the code below is the whole one you could paste to use directly, pay attention to the bold character.

let
Source = AdobeAnalytics.Cubes([HierarchicalNavigation=true]),
***** = Source{[Name="*****"]}[Data],
***** = *****{[Id="*****"]}[Data],
#"Added Items" = Cube.Transform(*****,
{
{Cube.ApplyParameter, "DateRange", {#date(2018, 10, 15), #date(2018, 10, 26)}},
{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"},
{Cube.AddMeasureColumn, "Shipping Revenue (e54)", "event54"},
{Cube.AddMeasureColumn, "Tax Revenue (e55)", "event55"}
}),
#"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}}),
selectrows=Table.SelectRows(#"Changed Type", each[revenue]>0)
in
  selectrows

Best Reagards

Maggie

[AOHORQUEZ]

Help please, want to do something similar but I can't.

-------------------------------

= Cube.Transform(SALDOS_INVENTARIOS1,
        {
          {Cube.ApplyParameter, "[DESC EMPRESA]", {"MIEMPRESA"}},
          {Cube.ApplyParameter, "[[Time].[Ano].[Ano]]", {"2020"}},

            {Cube.AddAndExpandDimensionColumn, "[Dim Company]", {"[Dim Company].[DESC EMPRESA].[DESC EMPRESA]"}, {"Company.Company"}},
            {Cube.AddAndExpandDimensionColumn, "[Dim Business Unit]", {"[Dim Business Unit].[BUSINESS UNIT].[BUSINESS UNIT]"}, {"Inv. Business Unit.Inv. BU Code."}},
            {Cube.AddAndExpandDimensionColumn, "[Dim Inv Items]", {"[Dim Inv Items].[DESC CAT].[DESC CAT]", "[Dim Inv Items].[DESC ITM].[DESC ITM]", "[Dim Inv Items].[INV ITEM ID].[INV ITEM ID]", "[Dim Inv Items].[DESCR TYPE].[DESCR TYPE]", "[Dim Inv Items].[UNIT MEASURE STD].[UNIT MEASURE STD]"}, {"Items.Inventory Category", "Items.Item", "Items.Item ID", "Items.Item Type", "Items.Unit Measure Std"}},
            {Cube.AddAndExpandDimensionColumn, "[Dim Storage Area]", {"[Dim Storage Area].[DESCR].[DESCR]", "[Dim Storage Area].[STORAGE AREA 1].[STORAGE AREA 1]"}, {"Storage Area.Storage Area Desc", "Storage Area.Storage Area Id"}},
            {Cube.AddAndExpandDimensionColumn, "[Time]", {"[Time].[Fecha Key].[Fecha Key]", "[Time].[Mes].[Mes]", "[Time].[Ano].[Ano]"}, {"Time.Date", "Time.Month", "Time.Year"}},
            {Cube.AddMeasureColumn, "BaseQty", "[Measures].[EXISTENCIAS]"},
            {Cube.AddMeasureColumn, "UnitCost", "[Measures].[CostUnitItemCalc]"}
        })

-------------------------------

Thank you