cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NAOS Regular Visitor
Regular Visitor

Differences between Query Editor and Data View

This is a very strange one. I have a very simple query with a few rows. In query editor shows 153 rows, which is the expected result according to my data source. On report view there are only 142. There is no filter whatsoever on report view. Anyone knows what could be causing this?

Thanks,
4 REPLIES 4
Community Support Team
Community Support Team

Re: Differences between Query Editor and Data View

Hi NAOS,

 

In your table, there must be some duplicate rows, right? In view mode, such as in a chart, all the duplicate rows will be a row.

 

Regards,

Jimmy Tao

NAOS Regular Visitor
Regular Visitor

Re: Differences between Query Editor and Data View

Hi @v-yuta-msft,

Thanks for your answer. Sorry I didn't use the correct naming standard. I meant "Data View" instead of "Report view". Unfortunately that's not the case. I have distinct values for each row, as each row is one country. I was even able to identify which rows were missing, but still have no explanation of why they are not being displayed on the Data view but they are in te query.

I'm trying a different apporach now as I have to have this working by Monday haha.

Thanks

Community Support Team
Community Support Team

Re: Differences between Query Editor and Data View

Hi NAOS,

 

Could you clarify more details of your query code and provide some screenshots?

 

Regards,

Jimmy Tao

NAOS Regular Visitor
Regular Visitor

Re: Differences between Query Editor and Data View

Hi @v-yuta-msft,

 
Sure no problem.  I already changed all the queries by now (because they were terrible) and the model works perfectly now. I still wonder why the difference between query editor and data view though, so if you can have a look at it would be awesome.

Here are the queries:
Data table (from here I reference other queries)

let
    Source = Folder.Files("FolderPath"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"ExtractContent" = Table.TransformColumns(#"Removed Other Columns",{"Content", each Excel.Workbook(_, null, true)}),
    #"Expanded Content" = Table.ExpandTableColumn(ExtractContent, "Content", {"Name", "Data"}, {"Content.Name", "Content.Data"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Content", each ([Content.Name] = "DATA INPUT")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Content.Name"}),
    #"Expanded Content.Data" = Table.ExpandTableColumn(#"Removed Columns", "Content.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67"}),

    #"Removed Top Rows" = Table.Skip(#"Expanded Content.Data",2),
    #"Removed Other Columns2" = Table.SelectColumns(#"Removed Top Rows",{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44"}),
    #"Transposed Table" = Table.Transpose(#"Removed Other Columns2"),
    #"Merged Columns1" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns1","Con","C / P",Replacer.ReplaceValue,{"Merged"}),
    #"Transposed Table1" = Table.Transpose(#"Replaced Value"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Removed Top Rows1" = Table.Skip(#"Promoted Headers",1),
    RemoveDots= Table.TransformColumns(#"Removed Top Rows1",{{ "BU", each Text.Remove(_,"…")}}),
    #"Renamed Columns" = Table.RenameColumns(RemoveDots,{{"ServiceCountry", "Country"}, {"No. of  locations", "Number of Locations"}, {"m² (1.000)", "Area (m²)"}, {"", "MinS"}, {"in country resource (qty)", "In-Country Resource (qty)"}, {"fly in resource (qty)", "Fly-in Resource (qty)"}, {"remote resource (qty)", "Remote Resource (qty)"}}),
    #"Sorted Rows1" = Table.Sort(#"Renamed Columns",{{"Country", Order.Ascending}}),
    #"Filtered Rows2" = Table.SelectRows(#"Sorted Rows1", each ([Country] <> null and [Country] <> "Country" and [Country] <> "Sample Country" and [Country] <> "Service" and [Country] <> "Service Category")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows2",{{"Consulting / Planning", Order.Descending},{"Opportunity Assessment", Order.Descending}}),
    Buffer = Table.Buffer(#"Sorted Rows"),
    #"Removed Duplicates" = Table.Distinct(Buffer, {"Country", "BU", "Region"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"Country", type text}, {"BU", type text}, {"Region", type text}, {"Number of Locations", Int64.Type}, {"Area (m²)", Int64.Type}, {"In-Country Resource (qty)", Int64.Type}, {"Fly-in Resource (qty)", Int64.Type}, {"Remote Resource (qty)", Int64.Type}}),
    #"Multiplied Column" = Table.TransformColumns(#"Changed Type", {{"Area (m²)", each _ * 1000, Currency.Type}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Multiplied Column","AMER","Americas",Replacer.ReplaceValue,{"Region"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","GER","Germany",Replacer.ReplaceText,{"Region"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Replaced Value2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(Record.RemoveFields(_,{"Country", "BU", "Region", "In-Country Resource (qty)", "Fly-in Resource (qty)", "Remote Resource (qty)"})), {"", null}))),
    #"Replaced Value3" = Table.ReplaceValue(#"Removed Blank Rows",null,0,Replacer.ReplaceValue,{"In-Country Resource (qty)"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",null,0,Replacer.ReplaceValue,{"Fly-in Resource (qty)"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4",null,0,Replacer.ReplaceValue,{"Remote Resource (qty)"})
in
    #"Replaced Value5"


This is the table with the strange behaviour (Countries):

let
    Source = Data,
    #"Filtered Rows1" = Table.SelectRows(Source, each ([BU] = "All BU")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Country", "Number of Locations", "Area (m²)"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Number of Locations] <> null and [#"Area (m²)"] <> null),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Country", Order.Ascending}})
in
    #"Sorted Rows"







Countries1.PNGQuery Editor 1

 

Countries2.PNGQuery Editor 2

 

Countries3.PNGQuery Editor 3

 

Countries4.PNGQuery Editor 4

 

CountriesDataView1.PNGData View 1

 

CountriesDataView2.PNGData View 2

 

CountriesDataView3.PNGData View 3

 

CountriesDataView4.PNGData View 4

 I hope this is useful. I'll keep the files in case you still need more info.