Are you tired of dealing with the frustration of lost number formatting in Excel Power Query after refreshing your data? You’re not alone! Many users have struggled with this issue, but fear not, dear reader, for we have a solution for you. In this article, we’ll dive into the world of Excel Power Query and explore the best practices for maintaining number formatting on new rows after refreshing your data.
Why Does This Happen?
Before we dive into the solution, let’s understand why this issue occurs in the first place. When you refresh your data in Power Query, it re-loads the data from the source and re-applies the transformations. This process can sometimes cause formatting to be lost, especially when it comes to number formatting.
Cause 1: Data Type Changes
One of the main reasons for lost number formatting is due to changes in data types. When Power Query refreshes the data, it may change the data type of a column, which can cause the formatting to be lost.
Cause 2: M Code
The M code, which is the language used to create Power Query formulas, can also cause formatting issues. If the M code is not correctly written, it can lead to lost formatting.
Solution: Using the “Format” Function
The “Format” function in Power Query is a powerful tool that allows you to maintain number formatting on new rows after refreshing your data. This function can be used in combination with other functions, such as “Add Column” or “Replace Values”, to achieve the desired formatting.
Example 1: Formatting a Single Column
Let’s say you have a table with a column called “Sales” and you want to format it as currency. You can use the following code:
= Table.AddColumn(
Table,
"SalesFormatted",
each Number.Format([Sales], "C0"))
This code adds a new column called “SalesFormatted” and formats the values in the “Sales” column as currency using the “C0” format.
Example 2: Formatting Multiple Columns
What if you have multiple columns that need to be formatted? You can use the “Format” function in combination with the “Add Column” function to achieve this:
= Table.AddColumns(
Table,
{
"SalesFormatted", each Number.Format([Sales], "C0"),
"RevenueFormatted", each Number.Format([Revenue], "C0")
}
)
This code adds two new columns, “SalesFormatted” and “RevenueFormatted”, and formats the values in the corresponding columns using the “C0” format.
Solution: Using the “Data Type” Function
An alternative solution to using the “Format” function is to use the “Data Type” function. This function allows you to specify the data type of a column, which can help maintain number formatting.
Example 3: Specifying a Data Type
Let’s say you have a column called “Date” and you want to ensure that it’s formatted as a date. You can use the following code:
= Table.TransformColumnTypes(
Table,
{{"Date", type date}}
)
This code specifies that the “Date” column should be formatted as a date, which helps maintain the correct number formatting.
Solution: Using the “M Code” Function
The M code function can also be used to maintain number formatting on new rows after refreshing your data. By writing the correct M code, you can ensure that the formatting is preserved.
Example 4: Using M Code
Let’s say you have a table with a column called “Sales” and you want to format it as currency. You can use the following M code:
let
Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
FormattedTable = Table.TransformColumns(
Source,
{"Sales", each Number.Format(_, "C0")}
)
in
FormattedTable
This M code transforms the “Sales” column and formats it as currency using the “C0” format.
Bonus Tip: Using Power Query Editor
The Power Query Editor is a powerful tool that allows you to create and edit Power Query formulas. By using the Power Query Editor, you can visualise the data and apply the formatting changes in a more intuitive way.
Step 1: Open Power Query Editor
To open the Power Query Editor, go to the “Data” tab in Excel and click on “From Table/Range” and then select “Query Editor”.
Step 2: Select the Column
Select the column that you want to format. You can do this by clicking on the column header.
Step 3: Apply Formatting
Right-click on the column and select “Format” and then select the desired format. For example, if you want to format the column as currency, you can select “Currency” and then choose the desired format.
Conclusion
Maintaining number formatting on new rows after refreshing your data in Excel Power Query can be a challenging task. However, by using the “Format” function, “Data Type” function, or M code, you can ensure that the formatting is preserved. Additionally, using the Power Query Editor can make the process more intuitive and visual. By following the examples and tips outlined in this article, you’ll be able to maintain correct number formatting and take your Power Query skills to the next level!
| Function | Description |
|---|---|
| Format | Formats a value as text using the specified format |
| Data Type | Specifies the data type of a column |
| M Code | Allows you to write custom formulas using the M language |
By mastering these functions and techniques, you’ll be able to tackle even the most challenging Power Query tasks with ease!
FAQs
Q: Why does my formatting disappear when I refresh my data?
A: This is usually due to changes in data types or incorrect M code. By using the techniques outlined in this article, you can maintain the correct formatting.
Q: Can I use the “Format” function on multiple columns at once?
A: Yes, you can use the “Format” function on multiple columns by using the “Add Columns” function or by specifying multiple columns in the “Format” function.
Q: What is the difference between the “Format” function and the “Data Type” function?
A: The “Format” function is used to format a value as text using a specific format, whereas the “Data Type” function is used to specify the data type of a column.
- Excel Power Query 2021
- Number formatting
- Refreshing data
- Format function
- Data Type function
- M Code
- Power Query Editor
Frequently Asked Question
Get the lowdown on number formatting on new rows after refresh in Excel Power Query 2021!
Why do my number formats disappear on new rows after refresh in Power Query?
It’s because Power Query doesn’t retain number formatting on new rows automatically. When you refresh your query, the formatting gets lost. But don’t worry, there’s an easy fix! You can use the “Format” section in the “Modeling” tab to maintain your number formatting.
How can I maintain number formatting on new rows in Power Query?
To keep your number formatting intact, go to the “Modeling” tab, click “Format,” and then select the column you want to format. From there, choose the desired format, and it will apply to both existing and new rows after refresh. Voilà!
Does Power Query 2021 support dynamic formatting based on conditions?
Yes, it does! Power Query 2021 allows you to create conditional formatting rules based on specific conditions. You can use the “Conditional Column” feature to apply dynamic formatting to your data. This way, your number formatting will adapt to changes in your data after refresh.
Can I use Power Query’s “Format” section to format multiple columns at once?
Absolutely! The “Format” section in Power Query allows you to select multiple columns and apply the same formatting to all of them simultaneously. Simply select the columns you want to format, choose the desired format, and Power Query will take care of the rest.
Are there any limitations to maintaining number formatting in Power Query?
While Power Query does an excellent job of maintaining number formatting, there are some limitations. For example, formatting rules might not be preserved when combining or merging queries. Additionally, some advanced formatting options might not be supported. Be sure to test your formatting rules after making changes to your query.
