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.
Need to split table like this:
a | b | 2 |
c | d | 3 |
to
a | b | 1 |
a | b | 1 |
c | d | 1 |
c | d | 1 |
c | d | 1 |
thanks for help a suggestions.
Solved! Go to Solution.
@xkapr Here are 2 ways to solve this, paste the whole code in the Advanced Editor in PQ.
let
Source =
Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText ( "i45WSlTSUUoCYiOlWJ1opWQgKwWIjZViYwE=", BinaryEncoding.Base64 ),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [ Text = _t, Text2 = _t, Value = _t ]
),
ChangedType =
Table.TransformColumnTypes (
Source,
{ { "Text", type text }, { "Text2", type text }, { "Value", Int64.Type } }
),
ColumnNames = Table.ColumnNames ( ChangedType ),
// This step helps in creating the data types for columns before the Expansion
ColumnAndTypes =
Expression.Evaluate (
"type table ["
& Text.Combine (
List.Transform (
Table.ToRecords ( Table.Schema ( ChangedType )[[Name], [Kind]] ),
each Expression.Identifier ( [Name] ) & " = nullable " & [Kind]
),
", "
)
& "]"
),
AddedCustom =
Table.AddColumn (
ChangedType,
"Custom",
each Table.Repeat ( Table.FromRecords ( { _ } ), _[Value] ),
ColumnAndTypes
),
RemovedColumns = Table.RemoveColumns ( AddedCustom, { "Text", "Text2", "Value" } ),
ExpandedCustom =
Table.ExpandTableColumn (
RemovedColumns,
"Custom",
ColumnNames,
ColumnNames
)
in
ExpandedCustom
let
Source =
Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText ( "i45WSlTSUUoCYiOlWJ1opWQgKwWIjZViYwE=", BinaryEncoding.Base64 ),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [ Text = _t, Text2 = _t, Value = _t ]
),
ChangedType =
Table.TransformColumnTypes (
Source,
{ { "Text", type text }, { "Text2", type text }, { "Value", Int64.Type } }
),
ColumnsAndTypes =
Expression.Evaluate (
"type table ["
& Text.Combine (
List.Transform (
Table.ToRecords ( Table.Schema ( ChangedType )[[Name], [Kind]] ),
each Expression.Identifier ( [Name] ) & " = nullable " & [Kind]
),
", "
)
& "]"
),
Transformation =
List.Transform (
Table.ToRecords ( ChangedType ),
each Table.Repeat ( Table.FromRecords ( { _ }, ColumnsAndTypes ), _[Value] )
),
Result = Table.Combine ( Transformation )
in
Result
HI! @xkapr
Please replace this code in the advance editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoCYiOlWJ1opWQgKwWIjZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}, {"Col3", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Col3"}, "Attribute", "Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Columns",{"Value", "Col3", "Attribute"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute"})
in
#"Removed Columns"
@xkapr Here are 2 ways to solve this, paste the whole code in the Advanced Editor in PQ.
let
Source =
Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText ( "i45WSlTSUUoCYiOlWJ1opWQgKwWIjZViYwE=", BinaryEncoding.Base64 ),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [ Text = _t, Text2 = _t, Value = _t ]
),
ChangedType =
Table.TransformColumnTypes (
Source,
{ { "Text", type text }, { "Text2", type text }, { "Value", Int64.Type } }
),
ColumnNames = Table.ColumnNames ( ChangedType ),
// This step helps in creating the data types for columns before the Expansion
ColumnAndTypes =
Expression.Evaluate (
"type table ["
& Text.Combine (
List.Transform (
Table.ToRecords ( Table.Schema ( ChangedType )[[Name], [Kind]] ),
each Expression.Identifier ( [Name] ) & " = nullable " & [Kind]
),
", "
)
& "]"
),
AddedCustom =
Table.AddColumn (
ChangedType,
"Custom",
each Table.Repeat ( Table.FromRecords ( { _ } ), _[Value] ),
ColumnAndTypes
),
RemovedColumns = Table.RemoveColumns ( AddedCustom, { "Text", "Text2", "Value" } ),
ExpandedCustom =
Table.ExpandTableColumn (
RemovedColumns,
"Custom",
ColumnNames,
ColumnNames
)
in
ExpandedCustom
let
Source =
Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText ( "i45WSlTSUUoCYiOlWJ1opWQgKwWIjZViYwE=", BinaryEncoding.Base64 ),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [ Text = _t, Text2 = _t, Value = _t ]
),
ChangedType =
Table.TransformColumnTypes (
Source,
{ { "Text", type text }, { "Text2", type text }, { "Value", Int64.Type } }
),
ColumnsAndTypes =
Expression.Evaluate (
"type table ["
& Text.Combine (
List.Transform (
Table.ToRecords ( Table.Schema ( ChangedType )[[Name], [Kind]] ),
each Expression.Identifier ( [Name] ) & " = nullable " & [Kind]
),
", "
)
& "]"
),
Transformation =
List.Transform (
Table.ToRecords ( ChangedType ),
each Table.Repeat ( Table.FromRecords ( { _ }, ColumnsAndTypes ), _[Value] )
),
Result = Table.Combine ( Transformation )
in
Result
Thank you. I injected this to my query.
let
Zdroj = Excel.CurrentWorkbook(),
Dny = Excel.CurrentWorkbook(){[Name="ondrej_dny_cp"]}[Content][Ondřej dny CP],
#"Filtrované řádky" = Table.SelectRows(Zdroj, each ([Name] = "ondrej_resitel")),
#"Rozbalené Content" = Table.ExpandTableColumn(#"Filtrované řádky", "Content", {"Partner", "Město", "Upřesnění", "Účel", "Km max", "Skutečná", "Jízd", "Km celkem"}, {"Content.Partner", "Content.Město", "Content.Upřesnění", "Content.Účel", "Content.Km max", "Content.Skutečná", "Content.Jízd", "Content.Km celkem"}),
#"Filtrované řádky1" = Table.SelectRows(#"Rozbalené Content", each ([Content.Km celkem] <> 0)),
#"Odebrané: Dolní řádky" = Table.RemoveLastN(#"Filtrované řádky1",1),
#"Odebrané sloupce" = Table.RemoveColumns(#"Odebrané: Dolní řádky",{"Name"}),
#"Přidané: Vlastní" = Table.AddColumn(#"Odebrané sloupce", "JízdSoučet", each [Content.Skutečná]+[Content.Jízd]),
#"Přeuspořádané sloupce" = Table.ReorderColumns(#"Přidané: Vlastní",{"Content.Partner", "Content.Město", "Content.Upřesnění", "Content.Účel", "Content.Km max", "Content.Skutečná", "Content.Jízd", "JízdSoučet", "Content.Km celkem"}),
#"Odebrané sloupce1" = Table.RemoveColumns(#"Přeuspořádané sloupce",{"Content.Skutečná", "Content.Jízd"}),
DoKnihyJizd = Table.RenameColumns(#"Odebrané sloupce1",{{"Content.Partner", "Partner"}, {"Content.Město", "Město"}, {"Content.Upřesnění", "Upřesnění"}, {"Content.Účel", "Účel"}, {"Content.Km max", "Km"}, {"Content.Km celkem", "Km celkem"}}),
DoKnihy= Table.SelectColumns(DoKnihyJizd,{"JízdSoučet"}),
ChangedType =
Table.TransformColumnTypes (
DoKnihy,
{ { "JízdSoučet", Int64.Type } }
),
ColumnsAndTypes =
Expression.Evaluate (
"type table ["
& Text.Combine (
List.Transform (
Table.ToRecords ( Table.Schema ( ChangedType )[[Name], [Kind]] ),
each Expression.Identifier ( [Name] ) & " = nullable " & [Kind]
),
", "
)
& "]"
),
Transformation =
List.Transform (
Table.ToRecords ( ChangedType ),
each Table.Repeat ( Table.FromRecords ( { _ }, ColumnsAndTypes ), _[JízdSoučet] )
),
Result = Table.Combine ( Transformation )
in
Result
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.