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 wou...
EricMEwald's avatar
EricMEwald
Regular Visitor
7 years ago

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's avatar
v-juanli-msft
Community Support
7 years ago

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's avatar
    AOHORQUEZ
    Frequent Visitor
    5 years ago

    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