User Profile
PwerQueryKees
Super User
Joined 2 years ago
User Widgets
Contributions
Re: Splitting Multiple Cells consecutive columns into separate rows
An alternative solution... Starting with the table "Antibiotics": Using this: let Source = AntiBiotics, // Unpivot the last 4 columns to get 4 rows per row with coumn name and value as "Attibute"and 'Value' #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"CaseID", "Principal Procedure", "Surgery Start Date", "Surgical Incision Time", "Surgery End Time", "Anesthesia Start Time", "Anesthesia End Time"}, "Attribute", "Value"), // Split the 'Value' column into its lines. #"Split Column in Rows by LF" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Columns", {{"Value", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"), // Split the line number from each line #"Split Column by Space" = Table.SplitColumn(#"Split Column in Rows by LF", "Value", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Line", "Value"}), // Change the data type of the line number to "Whole Number" to allow sorting and to identify the line #"Changed Type on Line" = Table.TransformColumnTypes(#"Split Column by Space",{{"Line", Int64.Type}}), // Recreate the 4 data columns, but keep the line number to ensure we get a unique row for each line #"Pivoted Column" = Table.Pivot(#"Changed Type on Line", List.Distinct(#"Changed Type on Line"[Attribute]), "Attribute", "Value") in #"Pivoted Column" results in: Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem. Remember: You can mark multiple answers as a solution... If I helped you, please click on the Thumbs Up to give Kudos. Kees Stolker A big fan of Power Query and ExcelRe: power query - loading files from different file paths
An alternative solution created through the UI entirely: let // Get all file details from the root folder Source = Folder.Files("C:\Users\keess\OneDrive\Documents\- Tools en Programmeren\Power Query\Fabric Community\power-query-loading-files-from-different-file-paths"), // Add a column for Station #"Add Station" = Table.AddColumn(Source, "Station", each Text.BetweenDelimiters([Folder Path], "\", "\", {1, RelativePosition.FromEnd}, 0), type text), // Add a column for the Year #"Add Year" = Table.AddColumn(#"Add Station", "Year", each Text.BetweenDelimiters([Folder Path], "\", "\", {2, RelativePosition.FromEnd}, 0), type text), // Set the data type of the year column to "Whole Number" #"Changed Type" = Table.TransformColumnTypes(#"Add Year",{{"Year", Int64.Type}}), // Filter the .xlsx files based on the parameters given #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Year] >= YearFrom and [Year] <= YearTo and [Station] = Station and [Extension] = ".xlsx") in #"Filtered Rows" Essentially: This gets all files in the root folder and it's subdirectories Adds 2 additional columns for the Station and the Year Filters Station, Year from the Parameters and file type (extension) .xlsx Ending up with the list of Excell files like this: Further processing can be done in several ways, but I assume you know what you want to do... Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem. Remember: You can mark multiple answers as a solution... If I helped you, please click on the Thumbs Up to give Kudos. Kees Stolker A big fan of Power Query and ExcelRe: populate a column from a previous value
And another possible solution.. Staring with: (note that the dates are provided as a string). Using this: let Source = ProductQuantityByMonth, // replace with your table #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductID", type text}, {"Year-Month", type date}, {"Quantity", Int64.Type}}), // The change type interprets the date string as an actual date #"Added Custom" = Table.AddColumn(#"Changed Type", "Last-Year-Month Quantity", each // we add a new column let last_year = Table.SelectRows(#"Changed Type", // get the previous month's row (row) => // a funtion with the current row as a parameter row[ProductID] = [ProductID] // for the same product and row[#"Year-Month"] = Date.AddYears([#"Year-Month"],-1)) // for the previous year [Quantity] // but only the column [Quantity] in List.SingleOrDefault(last_year) // returns the first and only element of the list, null when empty or generarate an error if there are more. You can change this to a List.Sum() if the same month can occur more than once for the same product. ) in #"Added Custom" Producing this:
