Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Icey
Community Support
Community Support

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.

View solution in original post

17 REPLIES 17
Anonymous
Not applicable

Hi @jimmy,

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

 

Thanks 

Magdy 

Hello @Anonymous 

 

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

 

Anonymous
Not applicable

Hi @Icey  & @Anonymous ,

 

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 @Anonymous 

 

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

 

BR

 

Jimmy

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

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 @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

Icey
Community Support
Community Support

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
Not applicable

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 @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

Anonymous
Not applicable

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 @Anonymous 

 

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

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

HI Nate,

 

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

Hello @Anonymous 

 

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

Anonymous
Not applicable

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

AlB
Super User
Super User

Hi @Anonymous 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors