Getting problems with nestedJoin

[Americo2018]

Hi,

I am having bad times with Power BI and nestedJoin. I have two tables each of one has a column of type whole number that are realted. In one table this column calls No_ and in the other calls Account No_ .

I am trying to create a nestedJoin using these two columns but I am getting this error message:

"DataFormat.Error: We couldn't convert to Number.
Details:
2000-6"

It doesn't say what column didin't worked but since I am using the before mentioned columns in the query I guess that one of them has problem.

This is the query I am using;

"= Table.NestedJoin(#"Changed Type",{"No_"},#"OITP Södertälje$G_L Entry",{"G_L Account No_"},"NewColumn",JoinKind.Inner)"

The Change Type step is the set to change the No_ type from text to whole number.

What can be the problem?

Regards

Americo

[v-chuncz-msft · Accepted solution]

@Americo2018,

You may check the related steps Table.TransformColumnTypes in The Advanced Editor.

[Americo2018]

Hi,

I don't know what should I look for, but this are the queries applied in both tables:

G_L Entry
let
    Källa = Sql.Databases("("xxxxxxxxxxx\prod"),
    DataWarehouse16 = Källa{[Name="DataWarehouse16"]}[Data],
    #"dbo_OITP Södertälje$G_L Entry" = DataWarehouse16{[Schema="dbo",Item="OITP Södertälje$G_L Entry"]}[Data],
    #"Ändrad typ" = Table.TransformColumnTypes(#"dbo_OITP Södertälje$G_L Entry",{{"G_L Account No_", Int64.Type}}),
    #"Filtrerade rader" = Table.SelectRows(#"Ändrad typ", each [G_L Account No_] > 2999),
    #"Lägg till egen" = Table.AddColumn(#"Filtrerade rader", "År", each Date.Year(DateTime.Date([Posting Date]))),
    #"Lägg till egen1" = Table.AddColumn(#"Lägg till egen", "Månad", each Date.MonthName(DateTime.Date([Posting Date]))),
    #"Filtered Rows" = Table.SelectRows(#"Lägg till egen1", each ([Source Code] <> "AVSLRESKTO")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Amount", type number}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Posting Date] <= Date.AddDays( Date.EndOfMonth( Date.AddMonths( DateTime.LocalNow(), 1 ) ), -1 ) )
in
    #"Filtered Rows1"

G_L Account
let
    Källa = Sql.Databases("xxxxxxxxxxx\prod"),
    DataWarehouse16 = Källa{[Name="DataWarehouse16"]}[Data],
    #"dbo_OITP Södertälje$G_L Account" = DataWarehouse16{[Schema="dbo",Item="OITP Södertälje$G_L Account"]}[Data],
    #"Ändrad typ" = Table.TransformColumnTypes(#"dbo_OITP Södertälje$G_L Account",{{"No_", Int64.Type}}),
    #"Ihopslagna frågor" = Table.NestedJoin(#"Ändrad typ",{"No_"},#"OITP Södertälje$G_L Entry",{"G_L Account No_"},"NewColumn",JoinKind.Inner),
    #"Expanderad NewColumn" = Table.ExpandTableColumn(#"Ihopslagna frågor", "NewColumn", {"G_L Account No_", "Posting Date", "Global Dimension 1 Code", "Global Dimension 2 Code", "Debit Amount", "Credit Amount", "År", "Månad"}, {"NewColumn.G_L Account No_", "NewColumn.Posting Date", "NewColumn.Global Dimension 1 Code", "NewColumn.Global Dimension 2 Code", "NewColumn.Debit Amount", "NewColumn.Credit Amount", "NewColumn.År", "NewColumn.Månad"}),
    #"Omdöpta kolumner" = Table.RenameColumns(#"Expanderad NewColumn",{{"NewColumn.Posting Date", "Posting Date"}, {"NewColumn.Global Dimension 1 Code", "Global Dimension 1 Code entry"}, {"NewColumn.Global Dimension 2 Code", "Global Dimension 2 Code entry"}, {"NewColumn.Debit Amount", "Debit Amount"}, {"NewColumn.Credit Amount", "Credit Amount"}, {"NewColumn.År", "År"}, {"NewColumn.Månad", "Månad"}}),
    #"Borttagna kolumner" = Table.RemoveColumns(#"Omdöpta kolumner",{"timestamp", "Account Type", "Global Dimension 1 Code", "Global Dimension 2 Code", "Income_Balance", "Debit_Credit", "No_ 2", "Blocked", "Direct Posting", "Reconciliation Account", "New Page", "No_ of Blank Lines", "Indentation", "Last Date Modified", "Totaling", "Consol_ Translation Method", "Consol_ Debit Acc_", "Consol_ Credit Acc_", "Gen_ Posting Type", "Gen_ Bus_ Posting Group", "Gen_ Prod_ Posting Group", "Picture", "Automatic Ext_ Texts", "Tax Area Code", "Tax Liable", "Tax Group Code", "Exchange Rate Adjustment", "Default IC Partner G_L Acc_ No", "Cost Type No_", "Auto_ Acc_ Group", "SRU-code", "Charge Type", "No_"}),
    #"Omdöpta kolumner1" = Table.RenameColumns(#"Borttagna kolumner",{{"NewColumn.G_L Account No_", "G_L Account No_"}}),
    #"Ändrad typ1" = Table.TransformColumnTypes(#"Omdöpta kolumner1",{{"Posting Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Ändrad typ1",{{"Posting Date", Order.Descending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [Posting Date] <= DateTime.Date( Date.AddDays( Date.EndOfMonth( Date.AddMonths( DateTime.LocalNow(), 1 ) ), -1 ) ) )
in
    #"Filtered Rows"

Do you see something wrong? 

Best regards,

Americo

[v-chuncz-msft]

@Americo2018,

As I mentioned, check step "Ändrad typ" and "Changed Type".

[Anonymous]

I have some input on this that may be helpful, can I still post though it is solved?

[Anonymous]

OK. My experience.

Same starting point, ""DataFormat.Error: We couldn't convert to Number." error on Nested join.

(The error message only displayed on Inner join not outer join).

I found the field with the error by stematically removing fields from the 2 merge sources until the error did not display.

The field can be confirmed as the error one by clicking the down arrow to filter that field and choosing "Load More". That gives the same error message.

Then you cal also use the 'Keep Rows' transform to show the Error rows. In my case the errors showed whereever the value was blank. Inserting a convert to text step did not prevent the errors.

In the end I found that the error was introduced into the source several queries before this one.

That is why converting to text did not work - I was attempting to convert an Error.

Reason for error: the original source (.csv) in my case has a field which I believed was numeric so in the query that loads the external file I manually added a Changed Type step to set it as numeric. The error was introduced into the field right there for all the blank values but did not surface until the inner merge step in a later query.

Solution: removed the Changed Type step for this field.

Regards

Dave