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