How do I append two sources in DAX code instead of the append button?

[shahid_tanmoy]

Hi All, 

I have two JSON sources. 
Normally I would have to create two seperate JSON sources and append the 2nd to the first. 
Can someone write me out the code to do it all in one? 

Thanks! 

Here is the first code, the 2nd code looks exactly the same just comes from a different system 

let
Source = Json.Document(Web.Contents(" URL IS HERE ")), 

(IS IT POSSIBLE TO JOIN THEM HERE, LIKE URL 1+ URL 2) 
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"account_name"}, {"data.account_name"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded data1",{{"data.account_name", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"data.account_name", "account_name"}})
in
#"Renamed Columns"

(OR IF THE FIRST ISINT POSSIBLE, THIS IS USUALLY WHERE THE APPEND SYNTAX IS THAT REFERS TO A DIFFERENT QUERY TABLE, IS IT POSSIBLE TO JUST UNION THE 2ND QUERY HERE) 

[v-jingzhang]

Hi @shahid_tanmoy 

Since the two sources are from different systems, I recommend that you remain them in two single queries. Power Query needs to treat them as from two data sources and it needs to store the credentials for these two data sources separately. 

I think your final purpose is to only keep the appended result in the model, right? To meet this goal, you can use Append Queries as New to have the appended result in a third query, then unselect "Enable load" option for the two source queries. In this way, only the third "Append1" query will be loaded into the model. The two source queries will be used when appending but won't be loaded after that. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[shahid_tanmoy]

I just want one table with 2 sources unioned together in code.  
I dont want to have to do 2 seperate tables and combined. 

[v-jingzhang]

Hi @shahid_tanmoy 

Ok, you may try this:

let
Source1 = Json.Document(Web.Contents(" URL IS HERE 1")), 
Source2 = Json.Document(Web.Contents(" URL IS HERE 2")), 

#"Converted to Table" = Table.FromRecords({Source1}),
#"Converted to Table2" = Table.FromRecords({Source2}),

CombineTables = Table.Combine({#"Converted to Table", #"Converted to Table2"}),

#"Expanded data" = Table.ExpandListColumn(CombineTables, "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"account_name"}, {"data.account_name"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded data1",{{"data.account_name", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"data.account_name", "account_name"}})
in
#"Renamed Columns"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[shahid_tanmoy]

so here is the thing, it works when the columns are the same,  but not when the 2nd source has different columns than the first, it gives me only the first table in the combine table. 

For example: 

if source 1 has account, impressions, conversionamount

if source 2 has account, impressions,revenueamount

it only gives me source 1 values, but if the column names are the same that error doesnt happen

[v-jingzhang]

Hi @shahid_tanmoy 

For the last column that has a different name, you want to combine them into the same column after appending or into two columns with their own names? If you want to combine them into the same column, you need to rename either of them because the Table.Combine() functions works based on the column names. If you want to have them in different columns, I think that is currently what Table.Combine() does. I haven't met the scenario that it would lose the second table that has different column names. 

Append queries - Power Query | Microsoft Learn

Table.Combine - PowerQuery M | Microsoft Learn

[shahid_tanmoy]

I want to the same column and I want to use the name of the first source as the column header. 

so possibly a code before the combine.tables line that changes the name of certain columns in the 2nd source if you can write out a code like that for me. 

[v-jingzhang]

Hi @shahid_tanmoy 

Please try this 

RenameColumns = Table.RenameColumns(#"Converted to Table2", {{"revenueamount", "conversionamount"}}),
CombineTables = Table.Combine({#"Converted to Table", RenameColumns}),

[shahid_tanmoy]

Hi, this didnt work, do you have to expand first or something? 

It says column not found but I know column exsists in the source 2, it gives me the error in the rename columns part. 

Can you rewrite this? 

let
Source1 = Json.Document(Web.Contents("https:/--Source1urlhere--account_name&_renderer=powerbi")),
Source2 = Json.Document(Web.Contents("https:/---Source2urlhere--account_name&_renderer=powerbi")),

#"Converted to Table1" = Table.FromRecords({Source1}),
#"Converted to Table2" = Table.FromRecords({Source2}),

#"RenameColumns" = Table.RenameColumns(#"Converted to Table2", {{"account_name", "account_name"}}),   GIVES ME AN ERROR HERE 

#"CombineTables" = Table.Combine({#"Converted to Table1", #"RenameColumns"})

in
#"CombineTables"

[v-jingzhang]

Hi @shahid_tanmoy 

The first name should be the old column name existing in the second table to be changed, and the latter name is the new name for it. If it says column not found, it means that it cannot find the first name from #"Converted to Table2". 

[shahid_tanmoy]

I know but the column does exsist in converted to table 2 cause if I expand it, its there.  And i just changed it from account name to account name for testing purposes. can you write out the whole code from the beginning to see if it works, cause i tried it again with another table where i changed device_name to account_name and even though the json table has device_name, this piece of code cannot find it. 

[jennratten]

It looks like your source step returns a record, so you can combine Source1 and Source2 like this:

CombineRecords = Record.Combine ( { Source1, Source2 } ),
#"Converted to Table" = Table.FromRecords ( CombineRecords )

[shahid_tanmoy]

Tried this, only gives me the second record