Forum Discussion

Americo2018's avatar
Americo2018
New Member
8 years ago
Solved

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

    • Americo2018's avatar
      Americo2018
      New 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