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.
In my data I have id-key, dimension and dimension value. Three columns total. I don't know how many different values dimension column holds (0,1,2...n). I have pivoted my data so that dimensions will be in columns. Next step is that I need to merge dimension values as one columns.
My data after pivot-operation:
id-key 1 2 3 4 5 0 Merged id001 a 3 aa true a - 3 - aa - - - true id002 5 rh zz false - 5 - rh - zz - - false
My problem is that I'm not sure how many columns I will have in this point. So, the following doesn't work.
= Table.AddColumn(#"Pivoted Column", "Merged", each Text.Combine({[1], [2], [3], [4], [5], [0]}, " - "), type text)
I'd like to use solution something like this:
= Table.AddColumn(#"Pivoted Column", "Merged", each Text.Combine({between([1], [0])}, " - "), type text)
All ideas are welcome!
Solved! Go to Solution.
A dynamic solution:
#"Added Custom" = Table.AddColumn( #"Pivoted Column", "Merged", each Text.Combine( List.Transform( List.Skip( Record.FieldValues(_)), each Replacer.ReplaceValue( Text.From(_), null, "")), " - "), type text)
My ideas was to start with:
Table.ColumnNames
List.Generate
Text.Combine
Haven't got any of these to work yet...
Hi @tondeli,
As I tested, please select all columns(no matter how many columns you have) by clicking "CTRL+A", then right click->Merge Columns->type the custom space as picture2 shown.
You will get expected result as follows.
It's easier to merge columns by interface navigation, rather than type the Query statement. After operation in interface, the Power Query statement will be generated automatically, you can click "Advanced Editor" and view the statements.
Best Regards,
Angelia
This solution gives me the following query line:
= Table.CombineColumns(#"Pivoted Column",{"id-key", "1", "2", "3", "4", "5", "0"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged")
It still "hard codes" those column names. If I remove one column this step gives arror. If I add new column this step skips that column in this step.
Result is ok, but I'd like to refer dynamic amount of columns in my data.
Just to be clear:
you didn't react to my solution as this is dynamic.
Hello @MarcelBeug,
I have a similar situation and I was wondering if you could help me with this.
I have the attached raw data and desired result here - https://drive.google.com/open?id=1fNQePPTGmQ8dDXTsQFwsSS6UMPb4Sss1
The output needs to be generated in two columns where Combiner.CombineTextByDelimiter will ignore the null values and if there is a null value there will be no delimiter.
I have worked on this by following code. But off course could not generate the output as I desired. If I could have worked the code as desired, at final step I would split the table on the first delimiter and I would have the output.
let Source = Excel.Workbook(File.Contents("C:\Users\smpa01\Documents\PBIX\Dynamic Merge Test.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Country", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Name", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Name"}, {{"Count", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"X",1,1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Country", "X"}, {"Custom.Name", "Custom.Country", "Custom.X"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Name"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.X", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.X", type text}}, "en-US")[Custom.X]), "Custom.X", "Custom.Country"), #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,"",Replacer.ReplaceValue,{"Custom.Name", "1", "2", "3", "4", "5", "6"}), Custom1 = Table.ToList(#"Replaced Value", Combiner.CombineTextByDelimiter(",")), #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"
Thank you in advance
Found it - https://community.powerbi.com/t5/Desktop/Combine-columns-if-not-null-or-empty/td-p/187969
A dynamic solution:
#"Added Custom" = Table.AddColumn( #"Pivoted Column", "Merged", each Text.Combine( List.Transform( List.Skip( Record.FieldValues(_)), each Replacer.ReplaceValue( Text.From(_), null, "")), " - "), type text)
@MarcelBeug Could you explain what the parts in your function does? I like the result but have a hard time adjusting it for my needs.
Best regards,
Alex
This is awesome.
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |