Forum Discussion

Brysonds's avatar
Brysonds
Helper III
7 years ago

Append Query not bringing in all data

Hi!   I have (3) queries in my application that represent 2017, 2018, and 2019 sales by period/year.   I appended queries 2 and 3 to query 1 to get a holistic view of all the data.    When I vi...
Need Help
smpa01's avatar
smpa01
Community Champion
7 years ago

Do you happen to have the sample data ?

smpa01's avatar
smpa01
Community Champion
7 years ago

If you use R there is another way to append using R.

 

R can be used to Append multiple tables. However, R can append tables when they have same Column Names and same number of Columns (as far as I know).

 

Power BI Append can append tables with dissimilar number of columns and dissimilar column names.

 

Example - tbl_A appended with tbl_B

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VczBDQAhCATAXngTI4voWYuh/zbkDh9ewmOT2WUtqlbiUKUTE5S72Bsaj2HkHIX2L9hz/LBeXJP0EO4leEqyyMeYJX5frMlo5L4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date " = _t, Price1 = _t, Price2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date ", type text}, {"Price1", type number}, {"Price2", type number}})
in
    #"Changed Type"

tbl_A

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcuxDcAwCETRXaiRBUewnVks9l/DJDSxlOqKd38tEjTpDaKdmPRm81yAJwWn6qFFeXnM0vynHFosX3Z+VXlWjHHGl1XsoIgN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date " = _t, Price1 = _t, Price2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date ", type text}, {"Price1", type number}, {"Price2", type number}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)df_B <- dataset#(lf)df_A <- dataset2#(lf)df_temp <- rbind(df_A, df_B)#(lf)#(lf)output <- df_temp",[dataset=#"Changed Type", dataset2=tbl_A]),
    output = #"Run R script"{[Name="output"]}[Value]
in
    output

tbl_B