topic Re: Data key - value in Power Query

Data key - value

acerNZ — Wed, 07 Oct 2020 09:41:49 GMT

Hi Experts

I just started, so hold my hand please. I have 2-3 questions

I got a straight forward data, but **bleep** for each ID, in alternate rows are data key and data values

This is what I did, I pivoted, merged and split now what I have is following columns

For each 1. ID(Repeated n times in rows), 2. Every second row Key (with alternate blank rows) 3. Name of the Key 4. Value with Every alternate row (Null, Null in the row corresponding to Key name, and in next row Value).

Please need help to move value column to move up in place of Null for each ID ..

So I have 3 coloumns

ID , Key name, Key value as attached in picture.

as shown in picture ( I will delete the key column).

Re: Data key - value

AlB — Wed, 07 Oct 2020 09:45:12 GMT

Hi acerNZ

Can you post your original table in text format, i.e. just do copy table in Power BI and paste it here. It can tehn be copied easily to run some tests and come up with the solution

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Data key - value

acerNZ — Wed, 07 Oct 2020 09:55:13 GMT

Hey AIB Super User,

Thanks a ton. Unfortunately, I have some confidential personal data in the reports, and hence created a dummy sample. Please can you help me with this, only thing to take care is I have ID rows going N times different for each ID... all I need is to value column to align in the row of Key name replacing null ( but I will wait for your advice).

Re: Data key - value

AlB — Wed, 07 Oct 2020 10:11:21 GMT

acerNZ

Paste the dummy then please

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Data key - value

Jimmy801 — Wed, 07 Oct 2020 10:22:09 GMT

Hello acerNZ

check out this solution.

it applies a replace-value to be sure empty strings are replaced with null. Then a Fill-down and a Fill-up is applied. The table is alternated, to keep every second row only

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRCsjIz0sF0kqxOjAhIDIyBAJjQyTB4MScxKJKhcS8vNLEHAz1piamUAEjTDONIGoswABJDIeRUOVmBgZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Type", type text}, {"Value", Int64.Type}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Type", "Value"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"Type"}), #"Filled Up" = Table.FillUp(#"Filled Down",{"Value"}), #"Removed Alternate Rows" = Table.AlternateRows(#"Filled Up",1,1,1) in #"Removed Alternate Rows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Re: Data key - value

acerNZ — Wed, 07 Oct 2020 13:45:51 GMT

Jimmy801 Oops my message did not go through.

It worked as charm, on the test file attached. But maybe my mistake, I did not explain it well

My actual data has for each ID, several hundreds of key and values in the next row, next coloumn and pattern is unknown.

I googled and found this https://youtu.be/V_ULyeHNJFY but this too will not work as for my problem

For each ID ( for each data key, the value is data key [Coloumn +1, Row+1])

I am not sure, how to achieve this.

Re: Data key - value

Jimmy801 — Wed, 07 Oct 2020 13:59:45 GMT

Hello

But this should have no impact how many keys you have for every id as one dataset is distributed on 2 rows and with fill up and down and alternating through the table should always work. The result should be as you were posting in your first post.

Jimmy