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.
Hi everyone,
I am having some troubles with this issue and after many failed tests I am reaching to you. I feel like I should use PowerQuery here but I am not sure.
I have a table with various products that have many caracteristics, including a range beginning and a range ending, and costs :
Product | Caracteristics 1 | Caracteristics 2 | Range beginning | Range end | Cost |
A | Z | 20 | 1 | 1 | 10 |
B | G | 12 | 3 | 2 | 10 |
C | D | 35 | 5 | 3 | 12 |
A | E | 40 | 8 | 8 | 30 |
What I want to do is to create a new table that would have the product information, its caracteristics, but with ligns for every number between the beginning and ending range, and that would divide the cost equally between each range.
Product | Caracteristics 1 | Caracteristics 2 | Range | Cost |
A | Z | 20 | 1 | 10 |
B | G | 12 | 3 | 5 |
B | G | 12 | 2 | 5 |
C | D | 35 | 5 | 4 |
C | D | 35 | 4 | 4 |
C | D | 35 | 3 | 4 |
A | E | 40 | 8 | 30 |
I would appreciate any advice !
Thank you 🙂
Solved! Go to Solution.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Caracteristics 1", type text}, {"Caracteristics 2", Int64.Type}, {"Range beginning", Int64.Type}, {"Range end", Int64.Type}, {"Cost", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {Number.From([Range end])..Number.From([Range beginning])}),
#"Expanded Range" = Table.ExpandListColumn(#"Added Custom", "Range"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Range",{"Range beginning", "Range end"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Caracteristics 2", type text}}, "en-IN"),{"Product", "Caracteristics 1", "Caracteristics 2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Merged=text, Cost=nullable number, Range=number]}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Cost", "Range"}, {"Cost", "Range"}),
#"Added Custom1" = Table.AddColumn(#"Expanded All", "Custom", each [Cost]/[Count]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Count", "Cost"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}, {"Merged.3", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "Product"}, {"Merged.2", "Characteristics 1"}, {"Merged.3", "Characteristics 2"}, {"Custom", "Cost"}})
in
#"Renamed Columns"
Hope this helps.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Caracteristics 1", type text}, {"Caracteristics 2", Int64.Type}, {"Range beginning", Int64.Type}, {"Range end", Int64.Type}, {"Cost", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {Number.From([Range end])..Number.From([Range beginning])}),
#"Expanded Range" = Table.ExpandListColumn(#"Added Custom", "Range"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Range",{"Range beginning", "Range end"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Caracteristics 2", type text}}, "en-IN"),{"Product", "Caracteristics 1", "Caracteristics 2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Merged=text, Cost=nullable number, Range=number]}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Cost", "Range"}, {"Cost", "Range"}),
#"Added Custom1" = Table.AddColumn(#"Expanded All", "Custom", each [Cost]/[Count]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Count", "Cost"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}, {"Merged.3", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "Product"}, {"Merged.2", "Characteristics 1"}, {"Merged.3", "Characteristics 2"}, {"Custom", "Cost"}})
in
#"Renamed Columns"
Hope this helps.
You are welcome.
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.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |