Forum Discussion
Need Help! Data transformation
Hi, Could anyone please help me to transform data in desired output in Power BI. Input Data ID COL1 COL1_Value COL2 COL2_Value COL3 COL3_Value 1 ServerName ABCDEF ServerName POI...
Thanks Anonymous and smpa01 for your help and solution.
Your solution works well in most of the cases. But I have few records where COL2 and COL3 values are not in order same as COL1.
For Example:
| ID | COL1 | COL1_Value | COL2 | COL2_Value | COL3 | COL3_Value |
| 1 | ServerName | ABCDEF | DiskName | E | ServerName | QWERTY |
| 2 | DiskName | D | ServerName | POIUYT | DiskName | F |
| 3 | Application | LKJ | Application | HYU |
Could you please check if this scenerio could also be handled.
Thanks,
Randhir
Completely dynamic M
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkst8kvMTVWK1YlWcskszoZzHAsKcjKTE0sy8/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Keywords to be searched for" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Keywords to be searched for", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Keywords to be searched for", "Keywords to be searched for"}})
in
#"Renamed Columns"Table4
let
Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Need-Help-Data-transformation/m-p/650169#M311784")),
Data2 = Source{2}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data2, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"COL1", type text}, {"COL1_Value", type text}, {"COL2", type text}, {"COL2_Value", type text}, {"COL3", type text}, {"COL3_Value", type text}}),
Custom1 = Table.DemoteHeaders(#"Changed Type"),
#"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns"),
Custom2 = Table.AlternateRows(#"Transposed Table",1,1,1),
#"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(Custom2, {"Column1"}, "Attribute", "Value"),
Custom3 = Table.AlternateRows(#"Transposed Table",0,1,1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Custom3, {"Column1"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","_Value","",Replacer.ReplaceText,{"Column1"}),
#"Merged Queries" = Table.NestedJoin(#"Replaced Value",{"Column1", "Attribute"},#"Unpivoted Other Columns1",{"Column1", "Attribute"},"Replaced Value",JoinKind.LeftOuter),
#"Expanded Replaced Value" = Table.ExpandTableColumn(#"Merged Queries", "Replaced Value", {"Attribute", "Value"}, {"Attribute.1", "Value.1"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Replaced Value",{"Column1", "Value", "Value.1"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Column1"}, {{"AD", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Accumulate(
[AD][Value.1],
"",
(state,current)=> (state &" "& current)
)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
#"Expanded AD" = Table.ExpandTableColumn(#"Removed Columns1", "AD", {"Column1", "Value", "Value.1"}, {"Column1", "Value", "Value.1"}),
#"Added Custom1" = Table.AddColumn(#"Expanded AD", "Custom.1", each let
CurrentText = [Custom],
Result= Table.SelectRows(Table4,each not Text.Contains(CurrentText, [Keywords to be searched for], Comparer.OrdinalIgnoreCase))
in
Result),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Keywords to be searched for"}, {"Keywords to be searched for"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.1", each if [Value.1]="" and [Keywords to be searched for]<>"" then [Keywords to be searched for] else [Value.1]),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom2",{"Column1", "Custom.1", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns1", List.Distinct(#"Removed Other Columns1"[Custom.1]), "Custom.1", "Value")
in
#"Pivoted Column"Desired Output
- Hi smpa01, Thanks for your time.
I am trying to understand your code. Could you please explain what is Table4 in query.
Result= Table.SelectRows(Table4,each not Text.Contains(CurrentText, [Keywords to be searched for]...
Actually "Added Custom1" step is failing with error "The name Table4 was not recognized".
Please help me to understand and resolved.
Thanks,
Randhir SinghYou need to create a query called Table4 with the following code for the output to execute
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkst8kvMTVWK1YlWcskszoZzHAsKcjKTE0sy8/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Keywords to be searched for" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Keywords to be searched for", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Keywords to be searched for", "Keywords to be searched for"}}) in #"Renamed Columns"Anonymous just wondering if you tried the code I posted.
