Forum Discussion
Moving Values of Several Rows within Column mostly empty to next Column
I just tried using Power Query, I have several Tables from different files, all have same structure and headers, then combine all of them in Power Query, now i want to move "blabla PER" and "blabla PBV"(Red Box) to Column "Stock Name" and its subsequent values(Green Box) to Column "Assets" so I can remove Column "Sector" and "Sub Industry Code".
The problem is while most table had empty values in column "Sector" and "Sub Industry Code", some tables had text and numbers within those columns which i don`t needed(I only needed values containing "(space)PER" and "(space)PBV" and its adjacent values, i tried using Conditional Column to make new column combining "blabla PER" and "blabla PBV"(Red Box) to Column "Stock Name", had both column (Sector and Stock Name) change type to text but it give me errors.
Please help 🙏
Hi Laosi ,
Thank you for reaching out to the Microsoft Fabric Community.
Your requirement is achievable in Power Query. You can restructure the summary rows (e.g., "Average PER", "Weighted Average PBV") from the Sector column into Stock Name, and move their corresponding values into the Assets column using the steps below:
Add a custom column to flag rows:
if Text.Contains([Sector], "PER") or Text.Contains([Sector], "PBV") then true else falseCreate new columns:
- New Stock Name:
if [IsSummaryRow] then [Sector] else [Stock Name]- New Assets:
if [IsSummaryRow] then [Assets] else [Assets]Once you’ve created the new columns, delete the original columns: Sector, Sub Industry Code, Stock Name, and Assets. Then rename your new columns to Stock Name and Assets. Make sure the data types are correct for example, text for names and numbers for values. These steps will clean up your data and remove the extra columns you don’t need.
If you still face issues after trying this, please share a sample of your data as suggested by lbendlin so we can help you further.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
3 Replies
all have same structure and headersdoesn't sound like they do?
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/1447523Hi Laosi ,
Thank you for reaching out to the Microsoft Fabric Community.
Your requirement is achievable in Power Query. You can restructure the summary rows (e.g., "Average PER", "Weighted Average PBV") from the Sector column into Stock Name, and move their corresponding values into the Assets column using the steps below:
Add a custom column to flag rows:
if Text.Contains([Sector], "PER") or Text.Contains([Sector], "PBV") then true else falseCreate new columns:
- New Stock Name:
if [IsSummaryRow] then [Sector] else [Stock Name]- New Assets:
if [IsSummaryRow] then [Assets] else [Assets]Once you’ve created the new columns, delete the original columns: Sector, Sub Industry Code, Stock Name, and Assets. Then rename your new columns to Stock Name and Assets. Make sure the data types are correct for example, text for names and numbers for values. These steps will clean up your data and remove the extra columns you don’t need.
If you still face issues after trying this, please share a sample of your data as suggested by lbendlin so we can help you further.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
Thank you for ur respond, i decided to move it manually in file sources so i can calculate market PER and PBV changing percentages as well, i`ll try it if i encounter same problem again in the future, any way thank you so much 🙏
