Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a query for bringing through values in power query. I have a table which has values in column 1, and some values repeated in column 2 - the rest of column 2 is made up of null values. I would like a column 3 which returns the column 2 value, and the values from column 1 immediately preceding and following it. I have tried merging the table with itself, but find it difficult to generate column 3 - for which I think there may be a more elegant solution.
Column 1 | Column 2 | Column 3 |
6 | null | 6 |
8 | 8 | 8 |
9 | null | 9 |
5 | null | null |
3 | null | null |
3 | null | 3 |
4 | 4 | 4 |
1 | null | 1 |
3 | null | null |
6 | null | 6 |
7 | 7 | 7 |
2 | null | 2 |
2 | null | null |
Solved! Go to Solution.
This isn't a very elegant solution but it's the first thing that I came up with:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlPSUVKK1YlWsgAyLMAsS5iQKYxhjMEwATJMwCxDDDm4keZAhjmYZQQTgjBiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", Int64.Type}, {"Column 2", Int64.Type}}),
Boolean = List.Transform(#"Changed Type"[Column 2], each if _ <> null then 1 else 0),
Combine = List.Transform(
List.Zip(
{
Boolean,
List.RemoveFirstN(Boolean, 1) & {0},
{0} & List.RemoveLastN(Boolean, 1)
}
), each List.Max(_)
),
AddToTable = Table.FromColumns(Table.ToColumns(#"Changed Type") & {Combine}, Table.ColumnNames(#"Changed Type") & {"Boolean"}),
AddColumn3 = Table.FromRecords(Table.TransformRows(AddToTable, each _ & [#"Column 3" = if [Boolean] = 1 then [Column 1] else null])),
#"Changed Type1" = Table.TransformColumnTypes(AddColumn3,{{"Column 1", Int64.Type}, {"Column 2", Int64.Type}, {"Boolean", Int64.Type}, {"Column 3", Int64.Type}})
in
#"Changed Type1"
This isn't a very elegant solution but it's the first thing that I came up with:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlPSUVKK1YlWsgAyLMAsS5iQKYxhjMEwATJMwCxDDDm4keZAhjmYZQQTgjBiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", Int64.Type}, {"Column 2", Int64.Type}}),
Boolean = List.Transform(#"Changed Type"[Column 2], each if _ <> null then 1 else 0),
Combine = List.Transform(
List.Zip(
{
Boolean,
List.RemoveFirstN(Boolean, 1) & {0},
{0} & List.RemoveLastN(Boolean, 1)
}
), each List.Max(_)
),
AddToTable = Table.FromColumns(Table.ToColumns(#"Changed Type") & {Combine}, Table.ColumnNames(#"Changed Type") & {"Boolean"}),
AddColumn3 = Table.FromRecords(Table.TransformRows(AddToTable, each _ & [#"Column 3" = if [Boolean] = 1 then [Column 1] else null])),
#"Changed Type1" = Table.TransformColumnTypes(AddColumn3,{{"Column 1", Int64.Type}, {"Column 2", Int64.Type}, {"Boolean", Int64.Type}, {"Column 3", Int64.Type}})
in
#"Changed Type1"
Thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |