Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.