Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a question that I really need help from experts.
I'm entering data for a table of values and temperatures, where:
That table has the structure as below.
Date | P01 | P02 | P01_T | P02_T | P03 | P04 | P03_T |
1/1/2023 | 1 | 3 | 23 | 30 | 3 | 7 | 27 |
1/2/2023 | 3 | 4 | 22 | 29 | 5 | 5 | 28 |
1/3/2023 | 2 | 5 | 25 | 29 | 3 | 3 | 26 |
Then I need to transform that "Input" table into a data structure like the "Output" table below but haven't found a way to do it.
Date | Code | Value | Temp |
1/1/2023 | P01 | 1 | 23 |
1/1/2023 | P02 | 3 | 30 |
1/1/2023 | P03 | 3 | 27 |
1/1/2023 | P04 | 7 | |
1/2/2023 | P01 | 3 | 22 |
1/2/2023 | P02 | 4 | 29 |
1/2/2023 | P03 | 5 | 28 |
1/2/2023 | P04 | 5 | |
1/3/2023 | P01 | 2 | 25 |
1/3/2023 | P02 | 5 | 29 |
1/3/2023 | P03 | 3 | 26 |
1/3/2023 | P04 | 3 |
Please help. Thank you very much.
Solved! Go to Solution.
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Custom1 = Table.UnpivotOtherColumns(Source,{"Date"},"x","y"),
Custom2 = Table.SplitColumn(Custom1,"x",each List.FirstN(Text.Split(Text.Replace(_,"_T","_Temperature")&"_Value","_"),2),{"Name","x"}),
Custom3 = Table.Pivot(Custom2,{"Value","Temperature"},"x","y",each _{0}?)
in
Custom3
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Custom1 = Table.UnpivotOtherColumns(Source,{"Date"},"x","y"),
Custom2 = Table.SplitColumn(Custom1,"x",each List.FirstN(Text.Split(Text.Replace(_,"_T","_Temperature")&"_Value","_"),2),{"Name","x"}),
Custom3 = Table.Pivot(Custom2,{"Value","Temperature"},"x","y",each _{0}?)
in
Custom3
Thank you, Daniel. I'm not proficient in coding, but from your code, I have grasped the concept. It's extremely interesting. Thanks a lot.
Unpivot all columns except date.
Split the Attribute column on '_'
Duplicate the table.
In version 1 of the table, keep only the rows that do not have a T value (Filter this from the column header)
In version 2 of the table, keep only the rows that have a T value.
Merge the tables together on date and attribute (Make sure you get the join right for your data - looks like a left join from the non-T table)
Thanks, HotChilli. The idea is very interesting, and I'll give it a try. I'd also like to share Daniel's idea:
Thank you for your help