Forum Discussion
Data key - value
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
JimmyJimmy801 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.
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
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.
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.
Br
Jimmy