Additional Column in one source table

[Anonymous]

Hi Sir,

I created one query with the help of Table 2 (shared in attachment) however i have another table where i have one column less.

Now i am unable to run the query due the error "column - Climate missing".

How can i solve this, please help.

There is certainity that a column might get added and might get missing in any table.

[smpa01]

@Anonymous  one way to resolve this problem is to define which are the columns that will be utilized in the query regardless of what gets added further in between these columns.

Let's suppose you have identified that no matter what additional column gets added to the source,  you are only going to need Name, ID, DOA and Country for the dataset. Once you have idetified that, you can ask PQ to discard everything else in the following way

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4oT1TSUTI0MjQCUkYGRoa6hga6BpZAjldiQWKeUqxOtFJIZi6Qb2xuiq7EOSMzLxGsJKi0Kh8oYGluga7GL7VcISo1MScxL0UpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, id = _t, doa = _t, country = _t]),
    Custom1 = Table.DemoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] = "id" or [Column1] = "name" or [Column1] = "doa" or [Column1] = "country" )),
    #"Transposed Table1" = Table.Transpose(#"Filtered Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"name", type text}, {"id", Int64.Type}, {"doa", type date}, {"country", type text}})
in
    #"Changed Type1"

Test out the above code by adding any additional columns wherever you like, it will only return Name, ID, DOA and Country

[Anonymous]

I am so sorry for replying you late, but just in case if i need all column from all/both tables then how we will do so that i can get all the columns from both the table.

Thanks

[ronrsnfld]

You need to edit your code (in the Advanced Editor) so that column naming becomes dynamic (varies with the column names) and not static (i.e: hard-coded). This commonly occurs in the UI generated code for steps like #"Changed Type" but can also occur in other types of steps.  The context of where that occurs determines how to deal with it.

[gtacchini]

Exactly what @Fowmy said. Please check the code of the query steps. It's very helpful if you enable the formula bar. The Formula Bar Inside Power BI Desktop - sqlitybi

Regards,

[Fowmy]

@Anonymous 

Not really sure how the steps are organized in your query but the error that you are getting is the column "Climate" has been used in one of the steps in your query, you need to identify the step and remove it.