cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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 23 store, but the powery i built doesn't read the new store and i don't know why and how 

Could any one help me with this?

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @Elzawawy84 ,

 

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.

View solution in original post

17 REPLIES 17
Frequent Visitor

Hi @jimmy,

Which part is wrong as i have copied and pasted the formula ?

 

Thanks 

Magdy 

Hello @Elzawawy84 

 

no, you didn't copy paste!

In this example you can see Table.columnNames twice

#"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]))
    ),

 

in your code it's not

Jimmy801_0-1607687914238.png

 

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

 

Frequent Visitor

Hi @Icey  & @JimmyM ,

 

I have added the steps as Icey recommended and it gave me these message below in every step.

Elzawawy84_0-1607686503474.png

 

Elzawawy84_2-1607686535865.png

 

Hello @Elzawawy84 

 

the formula is wrong. Check out the solution of  @Icey  and just copy paste please

 

BR

 

Jimmy

Helper V
Helper V

My guess would be your filter step, each [Column2] <> null. Try removing that step. 

Frequent Visitor

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.

Elzawawy84_0-1607095980093.png

 

Hello @Elzawawy84 

 

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

Community Support
Community Support

Hi @Elzawawy84 ,

 

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.

View solution in original post

Frequent Visitor

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.

Elzawawy84_0-1606994547630.png

 

Elzawawy84_1-1606994565469.png

 

 

 

Hello @Elzawawy84 

 

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

Frequent Visitor

Hi Jimmy, 

I built monthly P&L over each other and in the recent P&L i have new column with new stores names but the query doesn't pick it it.

Please see below all the steps i applied.

Elzawawy84_1-1606987751632.png

 

Hello @Elzawawy84 

 

you are invoking a custom function in step 3 (to read original files) and a filter-step. Most probably there your stores are filtered out. Check this 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

Helper V
Helper V

If this is from a csv file, go to your Source step, and remove the "Columns = 20". 
--Nate

HI Nate,

 

We have thtree more stores and they don't appear in the table

Hello @Elzawawy84 

 

most probably you have a filter-step somewhere in your query. If you have a CSV-file with all your stores in, then Power query will read them.


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

Frequent Visitor

Below are bpixs of what i have 

Elzawawy84_0-1606921088280.png

and this is the final table and it doesn't pick the new stores under the store column

Super User III
Super User III

Hi @Elzawawy84 

unless you share the pbix or the M code of what you are doing I don't see how we'll be able to help

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors