0

I have a table in power BI that looks like this: power query source table

I'm trying to change the "Series Name" column into individual separate new columns based on the values in "Series Name". There are 4 distinct values in this column:

  • "List"
  • "All"
  • "Ordered"
  • "Rejected"

I'm a specifically wanting to retain each individual data point in each series - I'm not wanting to sum or aggregate etc.

My issue is, whilst each of the series all have some data for each of the values listed in "Hire" column, each series has a different number of instances in each e.g.

Series "List" only has 1 data point for each "Hire" category:

Tier05...(count=1), Tier06..(count=1), Tier07..(count=1), Tier08...(count=1), Tier09...(count=1), Tier10...(count=1), but

Series "All" has multiple and varying No of data point for all the categories:

e.g. Tier05... (count=358), Tier06.. (count=274), Tier07..(count=305), Tier08...(count=928), Tier09...(count=1261), Tier10...(count=1171).

In essence I'm looking for a way that generates a null value in each of the new series column ("List", "All", "Ordered", "Rejected") if there is not sufficient data points to populate all the rows in that specific series column based on the "Hire" column values.

The output I'm looking for should look like this (Index column added to help visualise how the table changes as the row No increase): Top section our required output table

Then further down the table it would look like this. Between row 2 (above) and row 358 (below) the values for "List" in these rows would be null.

The same principle would apply on series "Ordered" and "Rejected" series but the no of rows and where these rows would be null is more varied. Series "List" is the worst case scenario.

Output table further down from row 357

Hope this makes sense?

Really hoping you can help me

Thank you in advance

Improve this question
1
  • 2
    What have you tried? There are numerous references for pivoting with no aggregation and avoiding errors, including some in this forum. Most involving adding an index column and grouping. What went wrong with your attempt? Also, posting a representative sample as text which could be copy/pasted would make it easier for those who might help. Commented Nov 3 at 20:10
Related questions
0
Concatenate list of column names and fill values up in Power Query
1
How to extract and organize multiple CSV file column values into a single table based on unique identifiers using Excel Power Query?
3
Power Query Replace null values with values from another column
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.