Awkward to Columnar - Filling a column values relatively placed in another column

[Anonymous]

Hi all,

I've got source data that comes in the following format:

           ID      XXXX

Date1 Val 1 Val 2 Val 3 Val 4 Val 5

Date2 Val 1 Val 2 Val 3 Val 4 Val 5

Date3 Val 1 Val 2 Val 3 Val 4 Val 5

           ID      YYYY

Date1 Val 1 Val 2 Val 3 Val 4 Val 5

Date2 Val 1 Val 2 Val 3 Val 4 Val 5

Date3 Val 1 Val 2 Val 3 Val 4 Val 5

          ID      ZZZZ

Date1 Val 1 Val 2 Val 3 Val 4 Val 5

Date2 Val 1 Val 2 Val 3 Val 4 Val 5

Date3 Val 1 Val 2 Val 3 Val 4 Val 5

And so on.

I want to transform this data into a continuous table in the following form:

ID      Date   

XXXX Date1 Val 1 Val 2 Val 3

XXXX Date2 Val 1 Val 2 Val 3

          ...

ID      Date   

YYYY Date1 Val 1 Val 2 Val 3

YYYY Date2 Val 1 Val 2 Val 3

So on. 

Or, ideally, fully columnar, like so:

ID      Date   Data

XXXX Date1 Val 1

XXXX Date 1 Val 2

XXXX Date 2 Val 1

XXXX Date 2 Val 2

YYYY Date 1 Val1

YYYY Date 2 Val 2

And so on.

The stage I'm stuck on is dynamically labelling up all of the rows that belong to a certain ID.

My table currently looks like this:

Index Modulo IDCol Date     Valu1 Valu2

1         1          XXXX 

2         2          0         Date 1  Val 1  Val 2

3         3          0         Date 2  Val 1  Val 2

4         1          YYYY

5         2          0         Date1  Val 1  Val 2

6         3          0         Date2  Val 1  Val 2

...

I was able to pull across the ID from the awkwardly placed column above the values into an ID column of its own using a conditional column, but I'm not sure how to fill down the correct IDs (replacing the 0s) for all consecutive dates until the days or the modulo counter resets.

Could anyone help?
Let me know if I need to provide more detail.

[Anonymous]

i'm not 100% sure as I've never tested it, but have you tried with the "unpivot" feature?

[Anonymous]

Thanks for the suggestion.

Unpivot definitely looks like it will be part of the solution in terms of achieving the full columnar look and distribution, but it doesn't help me with correctly labelling up the data ranges associated with specific IDs.

The data is now basically:

ID

XXXX 

0        Date1 Val1

0        Date1 Val2

0        Date2 Val1

0        Date2 Val2

YYYY  

0        Date1 Val1

0        Date1 Val2

0        Date2 Val1

... so on.

Any ideas on how to fill down the IDs?

[Anonymous]

If this is your model of a set of rows

XXXX 

0        Date1 Val1

0        Date1 Val2

0        Date2 Val1

0        Date2 Val2

and basically relying only on the "phisical order" to match the data, it's very dangerous. If that's what Unpivot does, then it's wrong.

Try to look at http://radacad.com/pivot-and-unpivot-with-power-bi

[Anonymous]

Well unfortunately my data comes in in this form:

	Property Address:	Address
	Property ID number:	ID number
DAY	00:00	00:30	01:00	01:30	02:00
01-11-2018	19438.789	19438.789	19438.789	19438.789	19438.789
02-11-2018	19440.122	19440.122	19440.122	19440.122	19440.122
03-11-2018	19455.582	19455.582	19455.582	19455.582	19455.582
04-11-2018	19463.472	19463.472	19463.472	19463.472	19463.472
	Property Address:	Address 2
	Property ID number:	ID number 2
DAY	00:00	00:30	01:00	01:30	02:00
01-11-2018	19330.842	19330.842	19330.842	19330.842	19330.842
02-11-2018	19332.147	19332.147	19332.147	19332.147	19332.147
03-11-2018	19345.829	19345.829	19345.829	19345.829	19345.829
04-11-2018	19353.225	19353.225	19353.225	19353.225	19353.225

Where, as you can see, the ID is not associated with each of the rows of data pertaining to it.

As a result, my first challenge is to just fill down the ID against each of the tabular sets of data.

Once I have done that, unpivot should work as intended, I think.

It's probably just my lack of experience with the Query Editor, but I've been stuck for a neat way of doing this for a few days now - any help would be appreciated.

[Anonymous]

Is that an Excel?

[Anonymous]

The source is an Excel file, yes.

But it comes in 50k rows at a time, hence why I'm looking for a Query Editor way of sorting it out into a workable format.

[itchyeyeballs]

In that scenario I have often cheated and created a hidden sheet in the Excel which uses formulas to "normalise" the data before import, that may not work in your case if you have lots of different 50k files

[Anonymous]

Thanks for weighing in guys.

I'd be really surprised if this couldn't be reliably processed in the Query Editor or M directly.

Surely there must be a way! 

I'm so close and yet so far away:

[parry2k]

@Anonymous i'm confident that this can be achieved using PQ. If you share your data in excel it will help to get you the solution.

[Anonymous]

Thanks for the suggestion, here's a link to a reduced verison of my source file: link removed as solution was found.

You should be able to download a good copy from there .

[Anonymous · Accepted solution]

Hi all - just to update, I managed to track down a solution.

If I changed my conditional column 'ID' to show null rather than '0' or blank ("") in the spaces between unique IDs (as per the screenshot in my reply here: https://community.powerbi.com/t5/Desktop/Awkward-to-Columnar-Filling-a-column-values-relatively-plac...), then I could simply use Fill -> Down to automatically replace all nulls with each consecutive non-null cell in the column.

Very clever, but not obvious at all as I tried filling down before and failed to get the right effect while I had '0' or "" in the empty fields.

[parry2k]

@Anonymous