Data loading more rows to Power BI than in the table in Google Big Query

[deedeedudu]

Hi ,

I connected a table of Google Big Query using native query that has 16k rows.

Then i applied a few transformations amongs which is Custom Column based on Text Between Delimiters, Merge Operations and finally returning the value based on a certain condition using index such as below:

= Table.AddColumn(#"Added Index2", "Question", each if Text.Contains(Text.Lower([msg]), "ok") then #"Added Index2"[msg]{[Index]+1} else "")

All transformations given below:

When i load the data to Power BI it takes infinity to load and the count of rows that is shown in the status goes to millions (refer below).

I want to understand why this is happening and how can i speed up data loading to Power BI.

[deedeedudu]

@Greg_Deckler , @MFelix - Please help

[MFelix]

Hi @deedeedudu,

This depends on the code you use has you refer. One of the steps that can be doubling your rows is the merging steps. 

But only looking at the steps names is difficult to pin point the error. 

[deedeedudu]

Here's the complete query:

let
    Source = Value.NativeQuery(GoogleBigQuery.Database(){[Name=<Name>]}[Data], "SELECT * FROM <GBQTable>", null, [EnableFolding=true]),
    #"Filtered Rows1" = Table.SelectRows(Source, each [filter] = FilterParameter),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows1", "datetime", "datetime - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"datetime - Copy", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"datetime - Copy", "date"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"datetime", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"contact_id"}, {{"Count", each _, type table [ account_number=nullable text, type=nullable text, json=nullable text, channel=nullable text, interaction=nullable text, datetime=nullable datetime, date=nullable date, Index=number]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"account_number", "type", "json", "channel", "interaction", "datetime", "date", "Index","filter"}, { "account_number", "type", "json", "channel", "interaction", "datetime", "date", "Index","filter"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"contact_id.1"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index.1", 1, 1, Int64.Type),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Index1",{"Index"}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Removed Columns1", "Text Between Delimiters", each Text.BetweenDelimiters([json], "text"":""", """,""timestamp"), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Text Between Delimiters.1", each Text.BetweenDelimiters([json], "msg"":""", """"), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text Between Delimiters1","%20"," ",Replacer.ReplaceText,{"Text Between Delimiters.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([type] <> "order")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Type_interactive", each if [type] = "interactive" then Text.BetweenDelimiters([json],"title\"":\""","\") else ""),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Keyword", each if [Type_interactive] <> "" then [Type_interactive] else if [Text Between Delimiters.1] <>"" then [Text Between Delimiters.1] else [Text Between Delimiters]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Text Between Delimiters", "Text Between Delimiters.1", "Type_interactive"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Keyword", "msg"}}),
    #"Added Index2" = Table.AddIndexColumn(#"Renamed Columns1", "Index", 0, 1, Int64.Type),
    #"Removed Columns3" = Table.RemoveColumns(#"Added Index2",{"Index.1"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns3", "Question", each if Text.Contains(Text.Lower([msg]), "ok") then #"Removed Columns3"{[Index]+1}[msg] else ""),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Response", each if not (Text.Contains(Text.Lower([Question]), "keyword") or [Question] = "" or Text.Contains(Text.Lower([Question]), "ok")) and #"Added Custom2" {[Index]+1}[contact_id] = #"Added Custom2"{[Index]+2} [contact_id] then #"Added Custom2"{[Index]+2}[msg] else "")

But i believe only the last transformation is creating problem which is   

= Table.AddColumn(#"Added Index2", "Question", each if Text.Contains(Text.Lower([msg]), "ok") then #"Added Index2"[msg]{[Index]+1} else "")

Before this it loads fine.

[deedeedudu]

Anyone to help here?