Forum Discussion
Solved
Powerbi
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYs7CsAgEAXvsrVFQv5nEQvNP5UggejpM1oJO8PwYLUWK0oO+D7UNmKUFkeeECNaykRwKaG5Gmx+n8qwUhe4/D2WaaNueF80VG8hoL4MO/WA96gTY34=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Level 6" = _t, #"Level 5" = _t, name = _t, id = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Level 6", "Level 5"}), RowCount = Table.RowCount(#"Replaced Value"), Level6NonNullList = List.RemoveNulls(#"Replaced Value"[Level 6]), Level6List = Level6NonNullList & List.Repeat({null},RowCount-List.Count(Level6NonNullList)), Level5NonNullList = List.RemoveNulls(#"Replaced Value"[Level 5]), Level5List = Level5NonNullList & List.Repeat({null},RowCount-List.Count(Level5NonNullList)), #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Level 6", "Level 5"}), Custom1 = Table.FromColumns({Level6List}&{Level5List}&Table.ToColumns(#"Removed Columns"),{"Level 6","Level 5"}&Table.ColumnNames(#"Removed Columns")) in Custom1
2 Replies
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYs7CsAgEAXvsrVFQv5nEQvNP5UggejpM1oJO8PwYLUWK0oO+D7UNmKUFkeeECNaykRwKaG5Gmx+n8qwUhe4/D2WaaNueF80VG8hoL4MO/WA96gTY34=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Level 6" = _t, #"Level 5" = _t, name = _t, id = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Level 6", "Level 5"}), RowCount = Table.RowCount(#"Replaced Value"), Level6NonNullList = List.RemoveNulls(#"Replaced Value"[Level 6]), Level6List = Level6NonNullList & List.Repeat({null},RowCount-List.Count(Level6NonNullList)), Level5NonNullList = List.RemoveNulls(#"Replaced Value"[Level 5]), Level5List = Level5NonNullList & List.Repeat({null},RowCount-List.Count(Level5NonNullList)), #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Level 6", "Level 5"}), Custom1 = Table.FromColumns({Level6List}&{Level5List}&Table.ToColumns(#"Removed Columns"),{"Level 6","Level 5"}&Table.ColumnNames(#"Removed Columns")) in Custom1here is another solution:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYs7CsAgEAXvsrVFQv5nEQvNP5UggejpM1oJO8PwYLUWK0oO+D7UNmKUFkeeECNaykRwKaG5Gmx+n8qwUhe4/D2WaaNueF80VG8hoL4MO/WA96gTY34=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Level 6" = _t, #"Level 5" = _t, name = _t, id = _t]), #"Ersetzter Wert" = Table.ReplaceValue( Source, "", null, Replacer.ReplaceValue, {"Level 6", "Level 5"} ), IndexX = Table.AddIndexColumn(#"Ersetzter Wert", "Index", 1, 1, Int64.Type), #"Andere entfernte Spalten" = Table.SelectColumns(IndexX, {"Level 6", "Level 5"}), GefilterteZeilen = Table.SelectRows(#"Andere entfernte Spalten", each ([Level 6] <> null)), LevelIndexX = Table.AddIndexColumn(GefilterteZeilen, "LevelIndex", 1, 1, Int64.Type), ZusammengeführteAbfragen = Table.NestedJoin( LevelIndexX, {"LevelIndex"}, IndexX, {"Index"}, "Merged", JoinKind.RightOuter ), ErweiterteMerged = Table.ExpandTableColumn( ZusammengeführteAbfragen, "Merged", {"name", "id"} ), EntfernteSpalten = Table.RemoveColumns(ErweiterteMerged, {"LevelIndex"}) in EntfernteSpalten
