Hello @Anonymous
you have to dig in the M-code to make things work. You can create your query (read from Workbook) and goto the advanved editor and replace your in statement with this code (Add a "," after your your code before pasting. Subsitute the #"Changed Type" with your last stepname.
ListSplit = List.Split(Table.ColumnNames(#"Changed Type"), 2),
CreateTableGroups = List.Transform( ListSplit, (lst)=> Table.FromColumns({Table.Column(#"Changed Type", lst{0}),Table.Column(#"Changed Type", lst{1})}, {"Value", "Date"})),
TableCombine = Table.Combine(CreateTableGroups)
in
TableCombine
this is the most straightforward you can do. You can also use a more professional way to handle it, to create a new function like this @Anonymous mentioned it already. Create a new blank query and copy paste this code there an name the query TransformYourTable
(tbl as table) =>
let
ListSplit = List.Split(Table.ColumnNames(tbl), 2),
CreateTableGroups = List.Transform( ListSplit, (lst)=> Table.FromColumns({Table.Column(tbl, lst{0}),Table.Column(tbl, lst{1})}, {"Value", "Date"})),
TableCombine = Table.Combine(CreateTableGroups)
in
TableCombine
then go to your query from the workbook, click on the table-symbol and select "invoke custom function"
Right click on your last step and select "Insert Step After"
go to the formula bar and put your new custom function
Before
After
Hope this helps
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
= Table.Combine(List.Transform(List.Split(Table.ToColumns(Source),2),each Table.FromColumns(_,{"Value","Date"})))
Hello @Anonymous
you have to dig in the M-code to make things work. You can create your query (read from Workbook) and goto the advanved editor and replace your in statement with this code (Add a "," after your your code before pasting. Subsitute the #"Changed Type" with your last stepname.
ListSplit = List.Split(Table.ColumnNames(#"Changed Type"), 2),
CreateTableGroups = List.Transform( ListSplit, (lst)=> Table.FromColumns({Table.Column(#"Changed Type", lst{0}),Table.Column(#"Changed Type", lst{1})}, {"Value", "Date"})),
TableCombine = Table.Combine(CreateTableGroups)
in
TableCombine
this is the most straightforward you can do. You can also use a more professional way to handle it, to create a new function like this @Anonymous mentioned it already. Create a new blank query and copy paste this code there an name the query TransformYourTable
(tbl as table) =>
let
ListSplit = List.Split(Table.ColumnNames(tbl), 2),
CreateTableGroups = List.Transform( ListSplit, (lst)=> Table.FromColumns({Table.Column(tbl, lst{0}),Table.Column(tbl, lst{1})}, {"Value", "Date"})),
TableCombine = Table.Combine(CreateTableGroups)
in
TableCombine
then go to your query from the workbook, click on the table-symbol and select "invoke custom function"
Right click on your last step and select "Insert Step After"
go to the formula bar and put your new custom function
Before
After
Hope this helps
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
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
