Delete, rename and change type for each column based in input file

[joshua1990]

Hi experts!

I have a table that contains like 300 columns.

Is it possible to delete, rename and change the data type based on a different input file that has a structure like this?:

Column	Active	Name	Type
A	TRUE	Size	Text
B	FALSE	Value	Number

It this possible with power query?

[Vijay_A_Verma · Accepted solution]

Let's take a table named "Input" as your first table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoJCnUFUsGZVakgXmpFiVKsTrSSE5Dj5ugTDJILS8wpBUn6leYmpRaBpZ0ROp0NkfW5IEkYoepxRTLS2RhZkxuSJhMg4ZhXqRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t, Active = _t, Name = _t, Type = _t])
in
    Source

The sample code for output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiUyA2U4rViVYyB7IsgNgSiA0NQARIraERWJKO2mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Size = _t, Value = _t, Column3 = _t, C1 = _t, C2 = _t, C3 = _t]),
    // Keep only those columns in Input which exist in Output
    FilteredInput = Table.SelectRows(Input, each List.Contains(Table.ColumnNames(Source), [Name])), 
    // Remove columns
    FilteredInputFalseList = Table.SelectRows(FilteredInput, each [Active]="FALSE"  )[Name],
    RemovedColumnsFromSource = Table.RemoveColumns(Source, FilteredInputFalseList),
    // Transform Column Types
    FilterInputTrueTable = Table.SelectRows(FilteredInput, each [Active] = "TRUE"),
    GetName = FilterInputTrueTable[Name],
    GetType = List.Transform(FilterInputTrueTable[Type], each Expression.Evaluate("type " & Text.Lower(_))),
    result = Table.TransformColumnTypes(RemovedColumnsFromSource, List.Zip({GetName, GetType}))
in
    result

The result Excel file is here which has the code - Open in Excel - Data - Queries and Connections - Then click on any query on right side

[Vijay_A_Verma]

A couple of question -

1. In Active column where True and False are mentioned - Does it have significance here?

2. In case of data type, the concept is always about change. Data type can't be deleted or renamed. Should I presume that you meant change only?

3. Does the column name need to be picked from Name column?

[joshua1990]

@Vijay_A_Verma THanks a lot for your help:

1. False = Remove the column; True = Keep the column

2. Change data dype for active columns

3. Yes