Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have an excel sheet named as Parent. From cell B1 to NC1 dates are mentioned. In cell A2 to A501, Tag Numbers are mentioned. Cell A1 is left intentionally. Animal Treatments are mentioned against each code number under the relevant date. Means treatments are mentioned in cells from 1Rx2C to 500Rx366C against relevant code under the relevant date. For example, if I do PG treatment of number A001 on 21st July 2023, I will type PG against goat number A001 under the date 21st July 2023. I will upload the excel file from get data from excel option in PowerBI.
Now I want to create “Data table” in PowerBI which contains Date in column 1 of that data table, Tag Numbers in Column 2, and Treatments in Column3. Like:
Tag Numbers | Treatments | Date |
A001 | PG | 07/01/2023 |
A002 | SELIVIT | 09/05/2023 |
A001 | OV | 09/16/2023 |
I want to Creat Data Table. Please Help!
Attached is the Screenshot of Excel File which i'll upload for data source.
Solved! Go to Solution.
Hi @DrayCount
You can refer to the following code to Advanced Editor in power query.
Sample data
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUQoI8g/xd/b3ATHdgQQQGQaHKIQGKwQEubtCBBAoVges0QjI9nUMCfH0c4fLEdZljC6uoxTs6uMZ5hkC5zs7ugY7Bnn6+8H0mGDqwTDXFK+aWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"2023/7/1" = _t, #"2023/7/2" = _t, #"2023/7/3" = _t, #"2023/7/4" = _t, #"2023/7/5" = _t, #"2023/7/6" = _t, #"2023/7/7" = _t, #"2023/7/8" = _t, #"2023/7/9" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"2023/7/1", type text}, {"2023/7/2", type text}, {"2023/7/3", type text}, {"2023/7/4", type text}, {"2023/7/5", type text}, {"2023/7/6", type text}, {"2023/7/7", type text}, {"2023/7/8", type text}, {"2023/7/9", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}, {"Column1", "Tag"}, {"Value", "Treatments"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Treatments] <> ""))
in
#"Filtered Rows"
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @DrayCount
You can refer to the following code to Advanced Editor in power query.
Sample data
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUQoI8g/xd/b3ATHdgQQQGQaHKIQGKwQEubtCBBAoVges0QjI9nUMCfH0c4fLEdZljC6uoxTs6uMZ5hkC5zs7ugY7Bnn6+8H0mGDqwTDXFK+aWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"2023/7/1" = _t, #"2023/7/2" = _t, #"2023/7/3" = _t, #"2023/7/4" = _t, #"2023/7/5" = _t, #"2023/7/6" = _t, #"2023/7/7" = _t, #"2023/7/8" = _t, #"2023/7/9" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"2023/7/1", type text}, {"2023/7/2", type text}, {"2023/7/3", type text}, {"2023/7/4", type text}, {"2023/7/5", type text}, {"2023/7/6", type text}, {"2023/7/7", type text}, {"2023/7/8", type text}, {"2023/7/9", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}, {"Column1", "Tag"}, {"Value", "Treatments"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Treatments] <> ""))
in
#"Filtered Rows"
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.