Forum Discussion

shahid_tanmoy's avatar
shahid_tanmoy
Icon for Helper I rankHelper I
3 years ago

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

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!    ...
Need Help
v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
3 years ago

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's avatar
    shahid_tanmoy
    Icon for Helper I rankHelper I
    3 years ago

    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's avatar
      v-jingzhang
      Icon for Community Support rankCommunity Support
      3 years ago

      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's avatar
        shahid_tanmoy
        Icon for Helper I rankHelper I
        3 years ago

        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