Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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
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
Hi @Icey & @Anonymous ,
I have added the steps as Icey recommended and it gave me these message below in every step.
Hello @Anonymous
the formula is wrong. Check out the solution of @Icey and just copy paste please
BR
Jimmy
My guess would be your filter step, each [Column2] <> null. Try removing that step.
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.
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
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.
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.
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
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.
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
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 @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
Below are bpixs of what i have
and this is the final table and it doesn't pick the new stores under the store column
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.