The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have these columns and values
Name Location Address City Country Age Email
John Yes 11 Main street NY US 23 a@gmail.com
Peter No 23 b@gmail.com
Mary No 56 c@gmail.com
As you can see, the values are not in the right columns sometimes!!
So I want to modify this table so that if Location = No, All of the values on the right should shift right by 3 columns.
For the last 3 columns that cannot be shifted, they should be ignored. Like this:
Name Location Address City Country Age Email
John Yes 11 Main street NY US 23 a@gmail.com
Peter No 23 b@gmail.com
Mary No 56 c@gmail.com
Is this even possible in PQ?
@Jimmy801 @Greg_Deckler @amitchandak @parry2k @Mariusz @ImkeF Thanks in advance!
Solved! Go to Solution.
Here is one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUYpMLQaShoYKvomZeQrFJUWpqSVAAb9IIBEaDCSMjIFEokN6bmJmjl5yfq5SrE60UkBqSWoRSFk+TEUSkgodJSgCKfVNLKqEqTQ1AxLJWFXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Location = _t, Address = _t, City = _t, Country = _t, Age = _t, Email = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let
recordaslist = List.RemoveMatchingItems(Record.ToList(_), {""})
in
if [Location] = "No" then Record.FromList(recordaslist, {"Name","Location", "Age", "Email"}) else Record.FromList(recordaslist, {"Name","Location", "Address", "City", "Country", "Age", "Email"})),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom", {"Name", "Location", "Address", "City", "Country", "Age", "Email"}, {"Name", "Location", "Address", "City", "Country", "Age", "Email"})
in
#"Expanded Custom"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks guys. Sorry I forgot to mention. My table has about 70 columns and 1 million rows. The Location column is quite early, around column number 10. Not sure which method would be most efficient but I will try them one by one.
Thanks for your suggestion. Your output table looks like this:
It seems the values 23 and 56 are in the wrong place. They should be in the Age column.
Also how can I do this using the interface??
Manually adding and removing columns?? Lol.
The meaning of your observation is not clear, but I try to explain better the meaning of what I meant (your level of knowledge of the power query environment is not even clear to me).
A solution that involves only the use of the UI means that you can practically obtain the necessary code by clicking on the mouse and not having to know and write the code manually.
In this case, it is a question of adding two new columns, deleting the old ones and reordering by displacing the new columns.
These operations are much easier than writing the code directly.
This looks to me as just a matter of importing this into Power Query using the correct delimiter. Those must be headers, so I'm sure that if you choose the right delimiter, this will all line up for you.
Here is another approach...
NextStep = Table.FromRows(List.Transform(Table.ToRows(Source),each if _{1}="No" then List.RemoveLastN(List.InsertRange(_,2,{"","",""}), 3) else _), Table.ColumnNames(Source))
Here is one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUYpMLQaShoYKvomZeQrFJUWpqSVAAb9IIBEaDCSMjIFEokN6bmJmjl5yfq5SrE60UkBqSWoRSFk+TEUSkgodJSgCKfVNLKqEqTQ1AxLJWFXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Location = _t, Address = _t, City = _t, Country = _t, Age = _t, Email = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let
recordaslist = List.RemoveMatchingItems(Record.ToList(_), {""})
in
if [Location] = "No" then Record.FromList(recordaslist, {"Name","Location", "Age", "Email"}) else Record.FromList(recordaslist, {"Name","Location", "Address", "City", "Country", "Age", "Email"})),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom", {"Name", "Location", "Address", "City", "Country", "Age", "Email"}, {"Name", "Location", "Address", "City", "Country", "Age", "Email"})
in
#"Expanded Custom"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks. Your code seems to work well although I did not use it on my database. I went back and seggreated my dataset from the beginning. A bit more manual work but then didn't have to do with codes.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
34 | |
19 | |
18 | |
16 | |
13 |