Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good evening folks,
I have data that is emailed to me daily with a grade and the price of the grade. My challenge is that the data is arrange in double columns that go across the page. How can I convert this data into just 2 columns, ie grade and average price? Please see example below, I wasn't sure how to attach a pdf or excel file. Any assistance greatly appreciated.
Regards
Herbert
Solved! Go to Solution.
Hi, if you don't need PDF name use this code:
let
// Google Drive Link
Source = Web.Contents("https://drive.google.com/uc?export=download&id=16en6fo7n-5VR-virg1DoezUzsRmHmYfi"),
ExcelWorkbook = Excel.Workbook(Source),
MatrixData_Table = ExcelWorkbook{[Item="MatrixData",Kind="Table"]}[Data],
Transformed = Table.Combine(List.Transform(List.Split(List.Skip(Table.ToColumns(MatrixData_Table)), 2), each Table.FromColumns(_, Value.Type(Table.SelectColumns(MatrixData_Table, List.FirstN(List.Skip(Table.ColumnNames(MatrixData_Table)),2))))))
in
Transformed
If you want to preserve PDF names, use this code:
let
// Google Drive Link
Source = Web.Contents("https://drive.google.com/uc?export=download&id=16en6fo7n-5VR-virg1DoezUzsRmHmYfi"),
ExcelWorkbook = Excel.Workbook(Source),
MatrixData_Table = ExcelWorkbook{[Item="MatrixData",Kind="Table"]}[Data],
GroupedRows = Table.Group(MatrixData_Table, {"Date"}, {{"All", each Table.Combine(List.Transform(List.Split(List.Skip(Table.ToColumns(_)), 2), (x)=> Table.FromColumns(x, Value.Type(Table.SelectColumns(_, List.FirstN(List.Skip(Table.ColumnNames(_)),2)))))), type table}}),
ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Grade", "Average Price"}, {"Grade", "Average Price"}),
FilteredRows = Table.SelectRows(ExpandedAll, each ([Grade] <> null and [Grade] <> "")),
ChangedType = Table.TransformColumnTypes(FilteredRows,{{"Grade", type text}, {"Average Price", Currency.Type}}, "en-US")
in
ChangedType
Hi @HerbertC, different approach:
Before
After
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjTy8VbSUTLSMwKSAcb+bo5A2sQUSEQY+7gDKVNjkAwIxepEK/kY+YDEzEzAqg3CgJQZVDFEtQlIdYSpQSSQMrcwAWvyNbAMBdlhCtbl4+ML0gViRwW7uII1mcKsiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Grade = _t, Average = _t, Grade1 = _t, Average1 = _t, Grade2 = _t, Average2 = _t, Grade3 = _t, Average3 = _t]),
Transformed = Table.Combine(List.Transform(List.Split(Table.ToColumns(Source), 2), each Table.FromColumns(_, Value.Type(Table.SelectColumns(Source, List.FirstN(Table.ColumnNames(Source),2))))))
in
Transformed
Import the Data: Open Excel and import the data from your email attachment or copy and paste it into a new worksheet.
Select the Data: Highlight the range of cells containing your data.
Open Power Query: Go to the "Data" tab in Excel and click on "From Table/Range" in the "Get & Transform Data" group. This will open the Power Query Editor.
Transform the Data:
Load the Transformed Data: Once you're satisfied with the transformation, click on "Close & Load" in the Home tab to load the data back into Excel.
Review the Data: After loading the transformed data back into Excel, you should see it in a format where you have two columns: one for the grade and one for the average price.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Thanks @jgeddes
The solution is not very far off. How can I attach an excel or CSV file so that you have the actuall data?
Regards
Herbert
The easiest way to share a file is to post a link to a file saved in cloud storage such as OneDrive. Make sure the link allows access to the file.
Another method for sample data is that you can copy a section of data frpm your file and pasting it into the table feature of the reply headers of these messages.
Proud to be a Super User! | |
hello, @HerbertC
to_list = Table.ToList(
your_table,
(x) => List.Split(x, 2)
),
to_table = Table.FromList(
List.Combine(to_list),
(x) => x,
{"Grade", "Average Price"}
)
// then filter out rows with nulls or blanks
Here is an example code that you can paste into the advanced editor of a blank query and work through the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjTy8VbSUTLSMwKSAcb+bo5A2sQUSEQY+7gDKVNjkAwIxepEK/kY+YDEzEzAqg3CgJQZVDFEtQlIdYSpQSSQMrcwAWvyNbAMBdlhCtbl4+ML0gViRwW7uII1mcKsiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Grade = _t, Average = _t, Grade1 = _t, Average1 = _t, Grade2 = _t, Average2 = _t, Grade3 = _t, Average3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Grade", type text}, {"Average", Int64.Type}, {"Grade1", type text}, {"Average1", Int64.Type}, {"Grade2", type text}, {"Average2", Int64.Type}, {"Grade3", type text}, {"Average3", Int64.Type}}),
Custom1 = List.Combine(Table.ToRows(#"Changed Type")),
Custom2 = List.Zip({List.Alternate(Custom1, 1, 1, 1), List.Alternate(Custom1, 1, 1, 0)}),
Custom3 = Table.FromRows(Custom2, type table [Grade = text, Average = number]),
#"Filtered Rows" = Table.SelectRows(Custom3, each ([Grade] <> ""))
in
#"Filtered Rows"
Basically you are creating a combining the lists of rows into a single list and then combining every other row together in a new list and then turning that list back into a table.
I start with...
and end up with...
Proud to be a Super User! | |
Thank you very much @jgeddes this is certainly getting me warmer.
I am a bit of a newbie, would you mind assisting to add your query to the one below, which I have extracted. The query below brings me to the starting point in power query which is equivalent to the pic that I originally posted:
let
Source = Folder.Files("C:\Users\XXXXX\Documents\XXX\Reporting & Analysis\XXXXXX Matrix\Data"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column2] <> null)),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"02-APR-2024.pdf", type text}, {"Grade", type text}, {"Average Price", type text}, {"Grade_1", type text}, {"Average Price_2", type text}, {"Grade_3", type text}, {"Average Price_4", type text}, {"Grade_5", type text}, {"Average Price_6", type text}, {"Grade_7", type text}, {"Average Price_8", type text}, {"Grade_9", type text}, {"Average Price_10", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Grade] <> "Grade")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"02-APR-2024.pdf", "Date"}})
in
#"Renamed Columns"
Thank you
This should do it...
let
Source = Folder.Files("C:\Users\XXXXX\Documents\XXX\Reporting & Analysis\XXXXXX Matrix\Data"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column2] <> null)),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"02-APR-2024.pdf", type text}, {"Grade", type text}, {"Average Price", type text}, {"Grade_1", type text}, {"Average Price_2", type text}, {"Grade_3", type text}, {"Average Price_4", type text}, {"Grade_5", type text}, {"Average Price_6", type text}, {"Grade_7", type text}, {"Average Price_8", type text}, {"Grade_9", type text}, {"Average Price_10", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Grade] <> "Grade")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"02-APR-2024.pdf", "Date"}}),
Custom1 = List.Combine(Table.ToRows(#"Renamed Columns")),
Custom2 = List.Zip({List.Alternate(Custom1, 1, 1, 1), List.Alternate(Custom1, 1, 1, 0)}),
Custom3 = Table.FromRows(Custom2, type table [Grade = text, Average = number]),
#"Filtered Rows2" = Table.SelectRows(Custom3, each ([Grade] <> ""))
in
#"Filtered Rows2"
Proud to be a Super User! | |
Good day @jgeddes @dufoq3 @johnbasha33 @AlienSx
Thank you all for your contributions so far, almost there but not quite.
I am sharing a link with my actual excel spreadsheet once I have done all the transformations that I can.
May you please assist with how to shift from here to end up with just 3 columns, i.e. 1. Source (Date), 2. Grade & 3. Price.
Thank you so much
Regards
Herbert
Hi, if you don't need PDF name use this code:
let
// Google Drive Link
Source = Web.Contents("https://drive.google.com/uc?export=download&id=16en6fo7n-5VR-virg1DoezUzsRmHmYfi"),
ExcelWorkbook = Excel.Workbook(Source),
MatrixData_Table = ExcelWorkbook{[Item="MatrixData",Kind="Table"]}[Data],
Transformed = Table.Combine(List.Transform(List.Split(List.Skip(Table.ToColumns(MatrixData_Table)), 2), each Table.FromColumns(_, Value.Type(Table.SelectColumns(MatrixData_Table, List.FirstN(List.Skip(Table.ColumnNames(MatrixData_Table)),2))))))
in
Transformed
If you want to preserve PDF names, use this code:
let
// Google Drive Link
Source = Web.Contents("https://drive.google.com/uc?export=download&id=16en6fo7n-5VR-virg1DoezUzsRmHmYfi"),
ExcelWorkbook = Excel.Workbook(Source),
MatrixData_Table = ExcelWorkbook{[Item="MatrixData",Kind="Table"]}[Data],
GroupedRows = Table.Group(MatrixData_Table, {"Date"}, {{"All", each Table.Combine(List.Transform(List.Split(List.Skip(Table.ToColumns(_)), 2), (x)=> Table.FromColumns(x, Value.Type(Table.SelectColumns(_, List.FirstN(List.Skip(Table.ColumnNames(_)),2)))))), type table}}),
ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Grade", "Average Price"}, {"Grade", "Average Price"}),
FilteredRows = Table.SelectRows(ExpandedAll, each ([Grade] <> null and [Grade] <> "")),
ChangedType = Table.TransformColumnTypes(FilteredRows,{{"Grade", type text}, {"Average Price", Currency.Type}}, "en-US")
in
ChangedType
@dufoq3 @mahenkj2 @jgeddes @johnbasha33 @AlienSx
My apologies, I have made the link public.
https://drive.google.com/drive/folders/1yLfb9-q0VF77zJDcVMyIp4VHgI0wHSIK?usp=sharing
Thank you
I've already solved your task 30 minutes ago... See it here if you don't want to scrolll