In Power BI, you can define tables manually using the #table function in Power Query. This is useful when you want to build sample data, test logic, or create quick examples without loading data from external sources. By combining lists and records, you can structure your table exactly how you need it.
In this article we will discuss various methods to create custom tables using #table with lists and records.
Method 1: Create Table using Columns with Lists:
let
Source = Table.TransformColumnTypes(
#table(
{"TextColumn", "NumberColumn", "DateColumn", "DateTimeColumn", "LogicalColumn", "DurationColumn", "TimeColumn", "BinaryColumn"},
{
{"Alpha", 100, #date(2025, 10, 1), #datetime(2025, 10, 1, 10, 0, 0), true, #duration(1, 2, 30, 45), #time(10, 0, 0), #binary({65,66})},
{"Beta", 200.5, #date(2025, 10, 2), #datetime(2025, 10, 2, 11, 15, 0), false, #duration(0, 5, 0, 0), #time(11, 15, 0), #binary({67,68})},
{"Gamma", -50, #date(2025, 10, 3), #datetime(2025, 10, 3, 9, 45, 0), true, #duration(2, 0, 0, 0), #time(9, 45, 0), #binary({69,70})}
}
),
{
{"TextColumn", type text},
{"NumberColumn", type number},
{"DateColumn", type date},
{"DateTimeColumn", type datetime},
{"LogicalColumn", type logical},
{"DurationColumn", type duration},
{"TimeColumn", type time},
{"BinaryColumn", type binary}
}
)
in
Source
Result:
-----------------------------------------------
Method 2: Create Table using Rows with Records:
let
Row1 = [TextColumn = "Alpha", NumberColumn = 100, DateColumn = #date(2025, 10, 1), DateTimeColumn = #datetime(2025, 10, 1, 10, 0, 0), LogicalColumn = true, DurationColumn = #duration(1, 2, 30, 45), TimeColumn = #time(10, 0, 0), BinaryColumn = #binary({65,66})],
Row2 = [TextColumn = "Beta", NumberColumn = 200.5, DateColumn = #date(2025, 10, 2), DateTimeColumn = #datetime(2025, 10, 2, 11, 15, 0), LogicalColumn = false, DurationColumn = #duration(0, 5, 0, 0), TimeColumn = #time(11, 15, 0), BinaryColumn = #binary({67,68})],
Row3 = [TextColumn = "Gamma", NumberColumn = -50, DateColumn = #date(2025, 10, 3), DateTimeColumn = #datetime(2025, 10, 3, 9, 45, 0), LogicalColumn = true, DurationColumn = #duration(2, 0, 0, 0), TimeColumn = #time(9, 45, 0), BinaryColumn = #binary({69,70})],
Source = Table.FromRecords({Row1, Row2, Row3}),
SourceWithType = Table.TransformColumnTypes(Source, {
{"TextColumn", type text},
{"NumberColumn", type number},
{"DateColumn", type date},
{"DateTimeColumn", type datetime},
{"LogicalColumn", type logical},
{"DurationColumn", type duration},
{"TimeColumn", type time},
{"BinaryColumn", type binary}
})
in
SourceWithType
Result:
-----------------------------------------------
Method 3: Create Table using Lists and Records:
let
_Names = {"Alpha", "Beta", "Gamma"},
_Indexed = List.Zip({_Names, {1..List.Count(_Names)}}),
_Rows = List.Transform(_Indexed, each [
TextColumn = _{0},
NumberColumn = _{1} * 100,
DateColumn = #date(2025, 10, 1),
DateTimeColumn = #datetime(2025, 10, 1, 10, 0, 0),
LogicalColumn = true,
DurationColumn = #duration(0, 1, 0, 0),
TimeColumn = #time(10, 0, 0),
BinaryColumn = #binary({65,66})
]),
table = Table.FromRecords(_Rows)
in
table
-----------------------OR------------------------
let
_Names = {"Alpha", "Beta", "Gamma"},
_Rows = List.Transform(_Names, each [
TextColumn = _,
NumberColumn = (List.PositionOf(_Names, _) + 1) * 100,
DateColumn = #date(2025, 10, 1),
DateTimeColumn = #datetime(2025, 10, 1, 10, 0, 0),
LogicalColumn = true,
DurationColumn = #duration(0, 1, 0, 0),
TimeColumn = #time(10, 0, 0),
BinaryColumn = #binary({65,66})
]),
table = Table.FromRecords(_Rows)
in
table
Result:
-----------------------------------------------
Method 4: Create Table using List.Combine method:
let
GroupA = {
[TextColumn = "Alpha", NumberColumn = 100, DateColumn = #date(2025, 10, 1)],
[TextColumn = "Beta", NumberColumn = 200, DateColumn = #date(2025, 10, 2)]
},
GroupB = {
[TextColumn = "Gamma", NumberColumn = 300, DateColumn = #date(2025, 10, 3)],
[TextColumn = "Delta", NumberColumn = 400, DateColumn = #date(2025, 10, 4)]
},
AllRows = List.Combine({GroupA, GroupB}),
RawTable = Table.FromRecords(AllRows),
TableWithType = Table.TransformColumnTypes(RawTable, {
{"TextColumn", type text},
{"NumberColumn", type number},
{"DateColumn", type date}
})
in
TableWithType
Result:
-----------------------------------------------
Method 5: Create Table using Table.Combine method:
let
TableA = Table.FromRecords({
[TextColumn = "Alpha", NumberColumn = 100, DateColumn = #date(2025, 10, 1)],
[TextColumn = "Beta", NumberColumn = 200, DateColumn = #date(2025, 10, 2)]
}),
TableB = Table.FromRecords({
[TextColumn = "Gamma", NumberColumn = 300, DateColumn = #date(2025, 10, 3)],
[TextColumn = "Delta", NumberColumn = 400, DateColumn = #date(2025, 10, 4)]
}),
CombinedTable = Table.Combine({TableA, TableB}),
TableWithType = Table.TransformColumnTypes(CombinedTable, {
{"TextColumn", type text},
{"NumberColumn", type number},
{"DateColumn", type date}
})
in
TableWithType
Result:
--------------------------------------------------------------------------------------------------------
Thanks
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. If you wish, please share your genuine Feedback or comments only related to this Blog Posts. It is my humble request that, please do not post any Spam comments or Advertising kind of comments, which will be Ignored.