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 a data base (excel) in which i need to replace errors with 0 - all base;
on future refreshments the data base will have new columns and i need now to set up this replacement for all base variable; otherwise for future columns the issue will remain.
Thanks!
Cosmin
Solved! Go to Solution.
I guess there is just a tiny "#" missing in step "Table_without_error":
let Source = Excel.Workbook(File.Contents("C:\Users\coscirnu\Desktop\lucru\Brand_Tracker\Sursa\All_Categories_All_Waves_Labels.xlsx"), null, true), All_Categories_All_Waves_Labels_Sheet = Source{[Item="All_Categories_All_Waves_Labels",Kind="Sheet"]}[Data], Table_without_error = Table.ReplaceErrorValues(#"All_Categories_All_Waves_Labels_Sheet", List.Transform(Table.ColumnNames("All_Categories_All_Waves_Labels_Sheet"), each {_, 0})) in Table_without_error
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
You can use the replace errors functionality of the Query Editor to replace errors with 0. For future columns, that's tricky, perhaps @ImkeF has a suggestion for that.
ok thanks
now i have almost 1500 columns which farther there are unpivoted
an automatically solution would be wonderfull
Hi @cosminc ,
a dynamic solution would be to add a step with with formula:
Table.ReplaceErrorValues(<YourTableName>, List.Transform(Table.ColumnNames(<YourTableName>), each {_, 0}))
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi,
can you help me with the right syntax?
i miss something and don't khow what.
Thanks a lot!
Cosmin
ps: the columns are not yet put on text or number format
let
Source = Excel.Workbook(File.Contents("C:\Users\coscirnu\Desktop\lucru\Brand_Tracker\Sursa\All_Categories_All_Waves_Labels.xlsx"), null, true),
All_Categories_All_Waves_Labels_Sheet = Source{[Item="All_Categories_All_Waves_Labels",Kind="Sheet"]}[Data],
Table_without_error = Table.ReplaceErrorValues("All_Categories_All_Waves_Labels_Sheet", List.Transform(Table.ColumnNames("All_Categories_All_Waves_Labels_Sheet"), each {_, 0}))
in
Table_without_error
I guess there is just a tiny "#" missing in step "Table_without_error":
let Source = Excel.Workbook(File.Contents("C:\Users\coscirnu\Desktop\lucru\Brand_Tracker\Sursa\All_Categories_All_Waves_Labels.xlsx"), null, true), All_Categories_All_Waves_Labels_Sheet = Source{[Item="All_Categories_All_Waves_Labels",Kind="Sheet"]}[Data], Table_without_error = Table.ReplaceErrorValues(#"All_Categories_All_Waves_Labels_Sheet", List.Transform(Table.ColumnNames("All_Categories_All_Waves_Labels_Sheet"), each {_, 0})) in Table_without_error
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
thanks,
there was another one
let
Source = Excel.Workbook(File.Contents("C:\Users\coscirnu\Desktop\lucru\Brand_Tracker\Sursa\All_Categories_All_Waves_Labels.xlsx"), null, true),
All_Categories_All_Waves_Labels_Sheet = Source{[Item="All_Categories_All_Waves_Labels",Kind="Sheet"]}[Data],
Table_without_error = Table.ReplaceErrorValues(#"All_Categories_All_Waves_Labels_Sheet", List.Transform(Table.ColumnNames(#"All_Categories_All_Waves_Labels_Sheet"), each {_, 0}))
in
Table_without_error
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.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |