Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

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

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?

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    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

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    3 years ago

    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's avatar
      joshua1990
      Icon for Post Prodigy rankPost Prodigy
      3 years ago

      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

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    3 years ago

    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