Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
Would it be possible to extract all the column names + 1st row, from each Table listed in 'Data' Column below please ?
The goal would be to have a list like:
Index0.Column01, 1st row Value
Index0.Column02, 1st row Value
Index0.Column03, 1st row Value
Index1.Column1, 1st row Value
Index1.Column2, 1st row Value
Index1.Column3, 1st row Value
Index2.ColumnA, 1st row Value
Index2.ColumnB, 1st row Value
Index2.ColumnC, 1st row Value
Index2.ColumnD, 1st row Value
Index3.Column1a, 1st row Value
Index3.Column2b, 1st row Value
Etc...
Solved! Go to Solution.
Hi, @Sayri , it's easy to extract all columns' names from each table in your scenario; you might add applied steps like this,
#"Column Names" = Table.TransformColumns(#"Previous Step", {{"Data", Table.ColumnNames}}),
#"Expanded Data" = Table.ExpandListColumn(#"Column Names", "Data")
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hello @Sayri
you can transform you tables first to get the first row of your tables as new table with Table.First and Record.ToTable.
Then expand the newly created table. here an easy example
let
Source = #table({"Index", "Data"}, {{"Index0", #table({"ColumnA", "ColumnB"}, {{"A", "B"}, {"C", "D"}})}, {"Index1", #table({"Column1", "Column2"}, {{"E", "F"}, {"G", "H"}})}}),
GetFirstRowOfTable = Table.TransformColumns
(
Source,
{
{
"Data",
each Record.ToTable(Table.First(_))
}
}
),
#"Expanded Data" = Table.ExpandTableColumn(GetFirstRowOfTable, "Data", {"Name", "Value"}, {"Column name", "First row value"})
in
#"Expanded Data"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Sayri
you can transform you tables first to get the first row of your tables as new table with Table.First and Record.ToTable.
Then expand the newly created table. here an easy example
let
Source = #table({"Index", "Data"}, {{"Index0", #table({"ColumnA", "ColumnB"}, {{"A", "B"}, {"C", "D"}})}, {"Index1", #table({"Column1", "Column2"}, {{"E", "F"}, {"G", "H"}})}}),
GetFirstRowOfTable = Table.TransformColumns
(
Source,
{
{
"Data",
each Record.ToTable(Table.First(_))
}
}
),
#"Expanded Data" = Table.ExpandTableColumn(GetFirstRowOfTable, "Data", {"Name", "Value"}, {"Column name", "First row value"})
in
#"Expanded Data"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Actually I've done a mix between your solution and the previous from @CNENFRNL and it works perfectly
Thank you very much to both of you
=#table(2,List.Combine(Table.ToList(PreviousStep,each List.Transform(Table.ColumnNames(_{1}),(x)=>Number.ToText(_{0},"Index0.")&x&","&Record.FieldOfDefault(_{1}{0},x,null)))))
Hi, @Sayri , it's easy to extract all columns' names from each table in your scenario; you might add applied steps like this,
#"Column Names" = Table.TransformColumns(#"Previous Step", {{"Data", Table.ColumnNames}}),
#"Expanded Data" = Table.ExpandListColumn(#"Column Names", "Data")
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks a lot @CNENFRNL , your solution works perfectly to list the column names 🙂
Is there any possibility to extract the 1st value of each colomn for each table please ?