Forum Discussion
Getting problems with nestedJoin
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
5 Replies
- v-chuncz-msftCommunity Support
- Americo2018New Member
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-msftCommunity Support