Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

New Columns to add

Hi All,   I have built power Query table which it gets the data from external file. I am importing in monthly P&L with Mulit location for example last month we had 20 store and this month we have ...
Need Help
Show and Tell
Tips and Tricks
  • Icey's avatar
    Icey
    5 years ago

    Hi Anonymous ,

     

    Try to change the step of "Expanded Table column1", like below and remove the step of "Changed type". 

    #"Expanded Data" = Table.ExpandTableColumn(
        #"Removed Other Columns1", "Transform File", 
        Table.ColumnNames(Table.Combine(#"Removed Other Columns1"[Transform File])),
        Table.ColumnNames(Table.Combine(#"Removed Other Columns1"[Transform File]))
    ),

     

    And remember to change the step "Trimmed Text" like so:

    #"Trimmed Text" = Table.TransformColumns(#"Expanded Data",{{"Profit & Loss", Text.Trim, type text}}),

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Jimmy,

 

Thanks for that, but How to change the code into some thing dynamically, i have already added more column for the new store but it gave me this message.

 

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Try to change the step of "Expanded Table column1", like below and remove the step of "Changed type". 

    #"Expanded Data" = Table.ExpandTableColumn(
        #"Removed Other Columns1", "Transform File", 
        Table.ColumnNames(Table.Combine(#"Removed Other Columns1"[Transform File])),
        Table.ColumnNames(Table.Combine(#"Removed Other Columns1"[Transform File]))
    ),

     

    And remember to change the step "Trimmed Text" like so:

    #"Trimmed Text" = Table.TransformColumns(#"Expanded Data",{{"Profit & Loss", Text.Trim, type text}}),

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hello Anonymous 

     

    the columns you added was in the change type of the expanded column. but you need to integrate it in the expandcolumn-function. Just take the approach Icey gave. this will work out

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy