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.
I have this as data.
clientid | Name | Email and Role |
C0003 | XYZ | xyz@gmail.in;Informed,Accountable |
C0003 | abc | abc@gmail.com;Informed,Accountable |
C0003 | Bikas | bikas@gmail.in;Responsible |
C0004 | Ram | ram@gmail.com;Informed |
C0007 | Patil | patil@xyz.com;Informed,Accountable |
C0007 | Padma | padma@xyz.com;Responsible |
I want the data in the following format pls help -
clientid | Name | Email and Role | Name | Email and Role | Name | Email and Role |
C0003 | XYZ | xyz@gmail.in;Informed,Accountable | abc | abc@gmail.com;Informed,Accountable | Bikas | bikas@gmail.in;Responsible |
C0004 | Ram | ram@gmail.com;Informed | ||||
C0007 | Patil | patil@xyz.com;Informed,Accountable | Padma | padma@xyz.com;Responsible |
You can try with these steps
Group = Table.Group(PreviousStep, {"clientid"}, {{"A", each let l = List.Union (Table.ToRows( Table.RemoveColumns(_, {"clientid"}))) in Text.Combine(l, "//"),
type text }}),
Split = Table.SplitColumn(Group, "A", Splitter.SplitTextByDelimiter("//", QuoteStyle.Csv)),
Count = {1..Number.RoundUp ((List.Count( Table.ColumnNames(Split)) - 1 )/2,0)},
Cols = List.Skip (Table.ColumnNames (Source)),
NewNames = {Table.ColumnNames (Source){0}} & List.Union ( List.Zip ({ List.Transform( Count, each Cols{0} & "_" & Text.From(_) ), List.Transform( Count, each Cols{1} & "_" & Text.From(_) ) })),
Custom = Table.RenameColumns ( Split, List.Zip ( { Table.ColumnNames(Split) , NewNames }) ),
ReplacedNulls = Table.ReplaceValue(Custom,null,"",Replacer.ReplaceValue,Table.ColumnNames (Custom))
in
ReplacedNulls
NewStep= Table.Combine(Table.Group(PreviousStepName,"clientid",{"n",each #table({"cliendid"}&List.TransformMany({1..Table.RowCount(_)},each List.Skip(Table.ColumnNames(_)),(x,y)=>y&" - "&Text.From(x)),{List.Combine(Table.ToList(_,List.Skip))})})[n])
Thanks. How to add this to current formula? pls help
Current formula as in advanced editor is -
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each [Column1.descp.1]&","&[Column1.descp.2]&","&[Column1.descp.3]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Combine(
List.Select(
{ [Column1.descp.1], [Column1.descp.2], [Column1.descp.3] },
each _ <> ""
),
","
)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Column1.descp.5", "Column1.descp.4", "Column1.descp.3", "Column1.descp.1", "Column1.descp.2", "Column2", "Column1", "Column1.isgroupinfo", "Column1.iscommu", "Column1.rtype", "Column1.id"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each [Column1.info]&";"&[Custom.1]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Column1.info", "Custom.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1.clientid", "clientid"}, {"Column1.attri", "Name"}, {"Custom", "Email and Role"}})
in
#"Renamed Columns"
how should i modify this?
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each [Column1.descp.1]&","&[Column1.descp.2]&","&[Column1.descp.3]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Combine(
List.Select(
{ [Column1.descp.1], [Column1.descp.2], [Column1.descp.3] },
each _ <> ""
),
","
)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Column1.descp.5", "Column1.descp.4", "Column1.descp.3", "Column1.descp.1", "Column1.descp.2", "Column2", "Column1", "Column1.isgroupinfo", "Column1.iscommu", "Column1.rtype", "Column1.id"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each [Column1.info]&";"&[Custom.1]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Column1.info", "Custom.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1.clientid", "clientid"}, {"Column1.attri", "Name"}, {"Custom", "Email and Role"}}),
NewStep= Table.Combine(Table.Group(#"Renamed Columns","clientid",{"n",each #table({"cliendid"}&List.TransformMany({1..Table.RowCount(_)},each List.Skip(Table.ColumnNames(_)),(x,y)=>y&" - "&Text.From(x)),{List.Combine(Table.ToList(_,List.Skip))})})[n])
in
NewStep
Expression.Error: We cannot convert the value 1 to type Table.
Details:
Value=1
Type=[Type]
getting this error
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.