Forum Discussion
Normalize monthly financial records into multiple records for producing line graph
I have a table with denormalized data by month (could have multiple lines per vendor with additional monthly cost).
I need to create a traditional line chart with month on the x-axis and dollars on the y-axis based on filtering the data by various other fields in the table ("Source" == 'Budget') or ("Source" == 'Actual') for example. How can I do this in PowerQuery?
Hey,
You can Achievethis by selecting the P[no] FY2024 columns and select Unpivot Columns in the transform columns tab of Power Query.
After that you can split the resulting Attribute column into both a Period and a Year Column.
I've used random values in the example.
Code snippet:let Source = YOURDATA, UnpivColumns = Table.UnpivotOtherColumns(Source, {"Vendor"}, "Period", "Value"), SplitColumn = Table.SplitColumn(UnpivColumns, "Period", Splitter.SplitTextByDelimiter(" FY", QuoteStyle.Csv), {"Period", "Year"}), ChangeType = Table.TransformColumnTypes(SplitColumn,{{"Period", type text}, {"Year", Int64.Type}}) in ChangeTypeoutcome:
5 Replies
- Omid_Motamedise
Super User
Select all the columns, right click on one of them and pick unpivot other columns, then create a pivot table based on the result table.
- Chewdata
Responsive Resident
Hey,
You can Achievethis by selecting the P[no] FY2024 columns and select Unpivot Columns in the transform columns tab of Power Query.
After that you can split the resulting Attribute column into both a Period and a Year Column.
I've used random values in the example.
Code snippet:let Source = YOURDATA, UnpivColumns = Table.UnpivotOtherColumns(Source, {"Vendor"}, "Period", "Value"), SplitColumn = Table.SplitColumn(UnpivColumns, "Period", Splitter.SplitTextByDelimiter(" FY", QuoteStyle.Csv), {"Period", "Year"}), ChangeType = Table.TransformColumnTypes(SplitColumn,{{"Period", type text}, {"Year", Int64.Type}}) in ChangeTypeoutcome:
- lbendlin
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 
SacheeThResolver II
To normalize your data for a line chart in Power BI, you need to unpivot the monthly columns into rows using Power Query. This transformation converts the wide table format (monthly data as columns) into a long format, with one column representing the months and another for the corresponding values (e.g., dollars).
After unpivoting, you can filter the data based on conditions like "Source == 'Budget'" or "Source == 'Actual'" and load it into Power BI.Once transformed, you can create a line chart by assigning the unpivoted month column to the X-axis and the values column to the Y-axis. Ensure the month column is sorted chronologically, and apply any necessary filters to refine your visualization.
Else pivot back to the original state which will filter the columns you have filtered on the fitering stage.Hi CLCrook ,
Did you find a solution? Did the above answers help? If so, mark the reply as a solution, which will help the next user with a similar problem, thank you~!
Best Regards,
Gao
Community Support Team
