Hi,
This M code works fine
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Line", Int64.Type}, {"Material Name", type text}, {"Quanity", Int64.Type}, {"Row", Int64.Type}, {"Pallets", Int64.Type}, {"Material Name 1", type text}, {"Quanity 1", Int64.Type}, {"Row 1", Int64.Type}, {"Pallets 1", Int64.Type}, {"Material Name 2", type text}, {"Quanity 2", Int64.Type}, {"Row 2", Int64.Type}, {"Pallets 2", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Line"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","Material Name","Material",Replacer.ReplaceText,{"Attribute.1"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Attribute.1]="Material" then [Value] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Custom", "Material"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Name", "Line", "Material", "Attribute.1", "Value"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Material", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Attribute.1] <> "Material")),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute.1]), "Attribute.1", "Value")
in
#"Pivoted Column"
Hope this helps.
Hi
try this M Code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8klNzM5LPLRASUfJCIid87NTwRxDEM8YiE2A2C0xrwSixtAAKmwKxMEFRZklqaiisTrRSi6JRVDVQOxbWgLUjdBsCLUpJKM0LyW1CGYVzDqv0szkVJigIRjHxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Line = _t, #"Material Name" = _t, Quantity = _t, Row = _t, Pallets = _t, #"Material Name 1" = _t, #"Quantity 1" = _t, #"Row 1" = _t, #"Pallets 1" = _t, #"Material Name 2" = _t, #"Quantity 2" = _t, #"Row 2" = _t, #"Pallets 2" = _t]),
ChangedType = Table.TransformColumnTypes(
Source,
{
{"Name", type text},
{"Line", Int64.Type},
{"Material Name", type text},
{"Quantity", Int64.Type},
{"Row", Int64.Type},
{"Pallets", Int64.Type},
{"Material Name 1", type text},
{"Quantity 1", Int64.Type},
{"Row 1", Int64.Type},
{"Pallets 1", Int64.Type},
{"Material Name 2", type text},
{"Quantity 2", Int64.Type},
{"Row 2", Int64.Type},
{"Pallets 2", Int64.Type}
}
),
Rowcount = Table.RowCount(ChangedType),
Records =
List.Accumulate(
{ 0..Rowcount - 1 },
{},
(state, current) =>
let
Rec1 = Record.SelectFields( ChangedType{current}, {"Name", "Line", "Material Name", "Quantity", "Row", "Pallets"} ),
Rec2 = Record.RenameFields(
Record.SelectFields( ChangedType{current}, {"Name", "Line", "Material Name 1", "Quantity 1", "Row 1", "Pallets 1"} ),
{ {"Material Name 1", "Material Name"}, {"Quantity 1", "Quantity"}, {"Row 1", "Row"}, {"Pallets 1", "Pallets" } }
),
Rec3 = Record.RenameFields(
Record.SelectFields( ChangedType{current}, {"Name", "Line", "Material Name 2", "Quantity 2", "Row 2", "Pallets 2"} ),
{ {"Material Name 2", "Material Name"}, {"Quantity 2", "Quantity"}, {"Row 2", "Row"}, {"Pallets 2", "Pallets" } }
),
Combined = List.Combine( { {Rec1}, {Rec2}, {Rec3} } )
in
List.Combine( {state, Combined } )
),
Final = Table.FromRecords (
Records, type
table [
Name = Text.Type,
Line = Int64.Type,
#"Material Name" = Text.Type,
Quantity = Number.Type,
Row = Number.Type,
Pallets = Number.Type
]
)
in
Final
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@Chanleakna123 if your other tables follow the same structure / logic, all you need to change is the first step called Source and point it to your raw table
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
