Power Query conversion from single columns to multiple columns based on rowname index

[kvbe]

For my datamodel I need to convert a table I fetch from a database into a more easy format to work with in Power BI.

The table I receive from the database is this one. (after some cleanup and transposing it)

CurYrPrdStat1	C
CurYrPrdStat2	C
CurYrPrdStat3	O
CurYrPrdStat4	C
CurYrPrdStat5	C
CurYrPrdStat6	C
CurYrPrdStat7	C
CurYrPrdStat8	C
CurYrPrdStat9	C
CurYrPrdStat10	C
CurYrPrdStat11	C
CurYrPrdStat12	C
CurYrPrdStat13	C
CurYrPrdStat14
CurYrPrdStat15	C
CurYrPrdEnd1	31/07/2018 00:00
CurYrPrdEnd2	31/08/2018 00:00
CurYrPrdEnd3	30/09/2018 00:00
CurYrPrdEnd4	31/10/2018 00:00
CurYrPrdEnd5	30/11/2018 00:00
CurYrPrdEnd6	31/12/2018 00:00
CurYrPrdEnd7	31/01/2019 00:00
CurYrPrdEnd8	28/02/2019 00:00
CurYrPrdEnd9	31/03/2019 00:00
CurYrPrdEnd10	30/04/2019 00:00
CurYrPrdEnd11	31/05/2019 00:00
CurYrPrdEnd12	30/06/2019 00:00
CurYrPrdEnd13	30/06/2019 00:00
CurYrPrdEnd14	null
CurYrPrdEnd15	null

Using power Query, I would like to achieve following format:

Period	CurrentPeriodEndDate	CurrentStatus
Period 1	31/07/2018 00:00	C
Period 2	31/08/2018 00:00	C
Period 3	30/09/2018 00:00	O
Period 4	31/10/2018 00:00	C
Period 5	30/11/2018 00:00	C
Period 6	31/12/2018 00:00	C
Period 7	31/01/2019 00:00	C
Period 8	28/02/2019 00:00	C
Period 9	31/03/2019 00:00	C
Period 10	30/04/2019 00:00	C
Period 11	31/05/2019 00:00	C
Period 12	30/06/2019 00:00	C
Period 13	30/06/2019 00:00	C
Period 14	null
Period 15	null	C

So basically what I would like to achieve is using the index number (eg. CurYrPrdEnd1) and use that one as a value. 

I tried but do not really see how I could convert this using the power query editor. 

After that I would like to use this table to augment my datetable I generated with the

period number added to the datetable. 

Something like:

01/07/2018 Period 1 

...

31/07/2018 Period 1

01/08/2018 Period 2 

etc. 

This using the period end date specified.

I have the date table etc and merging it with this new table I should be able to manage, just sure about the two questions regarding getting it in the right format.

[v-lili6-msft · Accepted solution]

hi,@kvbe

    After my research, you can do these follow my steps as below:

Basic data

Step1:

Select column A and duplicate this column

and then select the duplicate column replace value

Step2:

duplicate the table

Step3:

Merge basic table with duplicate table

And then expand the table

Step4:

Add a conditional column

Step5:

filter custom column is 2

Step6:

Add the index column

Step7:

select Index column ,click Add Column->Standard->Modulo

Step8

filter column Modulo is 0

Step9:

select Column A and replace the value

Step10:

Remove unnecessary column and rename the column

Result:

here is demo, please try it.

https://www.dropbox.com/s/ablf38rvrhravgf/Power%20Query%20conversion%20from%20single%20columns%20to%...

Best Regards,

Lin

[kvbe]

Hi Lin,

Thank you very much thats a neat way of approaching this! 

Never thought about the merging + index + modulo approach. 

Another type of technique I can add to my power query toolbox.