All…this one has me a little stumped! I have a badly-structured Excel corporate report that we need to visualize in PBI. My struggle is with how the “Project Name” field is set up. This field allows users to type up to three rows for the the project name while the other fields contain categories of data (Budget, Forecast, Actual). The data is brought in from many worksheets in a single workbook and contains more than 1,000 projects. A transform-merge columns-transform strategy does not work for this data because the number of projects is dynamic.
I’ve attached a small source and PBIX with two datasets: one named source and one named needed. These should illsutrate the problem. I’d appreciate and suggestions the community might have to deal with this!
Attached PBIX has a solution in it which works with your sample and assuming the raw data always comes with the project name spread over 3 rows ordered correctly will work.
It applies an index column, then concatenates the text of the previous 2 rows and current row where the modulus (remainder) is zero. ie every third row. This is then filled up to apply the full project name to all rows.
Then a little bit of unpivot puts your data into the desired shape
Yes, the proposed solution was wonderful. I almost had it before posting using Number.Mod but was trying to get the Project Description on the first line of the group rather than the third. This is why the community is so helpful in proposing other approachs.
One note: My live data may have had data on only one or two of the three rows populated, so simple & concatenation didn’t work. It took me a bit to realize the problem but with your emaple and a little troubleshooting, Text.Combine got me the desired results!
