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,

I have checked this and the stores are not filttered out, so i have added new column for the new stores (Highlighted in Yellow)  in the formula as below but i gave this message.

 

 

 

 

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

Hello Anonymous 

 

so a new store is resulting in new columns, not new rows? I saw that the reading of your file was created with the automatic function of Power Query to combine files automatically. In this code Power query is expanding only columns that are available in the first file (Sample file). If you have your new stores only in other files, this code will not work out. You would need to change your code into something dynamically. 

 

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